ConvertEmptyStringToNull

I have a DB table with a varchar field that doesn't allow nulls, but the SqlDataSource I use to update the row wants to put a null in in exchange of an empty string when the field is empty.

<asp:Parameter Name="comment" ConvertEmptyStringToNull="false" Type="string" /
I even tried settting ConvertEmptyStringToNull to "false" but got nowhere.

I can do this by directly by handling the Updating event of the SqlDataSource, but that's a lot of code. Is there a better way?

I don't know whether this is what you are looking for. Use a space for the DefaultValue.

<UpdateParameters>

<asp:ParameterName="comment"DefaultValue=" "Type="String"/></UpdateParameters>|||

You have to set to ConvertEmptyStringToNull="false" on both the Parameter, and the BoundField control.

For example:

<

asp:BoundFieldDataField="locationName"HeaderText="Location Name"SortExpression="locationName"ConvertEmptyStringToNull="false"/>

<

asp:ParameterName="locationName"Type="String"ConvertEmptyStringToNull="false"/>|||

Hi,

This was a great help, I have been teaching myself ASP.NET and it was the first stumbling block. I was following "SAMS Teach Yourself ASP.NET (24Hrs)" and was on the datagrid view section. It was advising me to set the column value (which was set to not allow nulls) to still be able to except a blank entry. This was supposedly achieved by editing the value via the smart tag and then setting the ConvertEmptyStringToNull, which I did. But still failed with the error "Null expected".

I followed the manual method described above and edited the source code and it worked!

My question is if this is a bug with Visual Studio Express? That is the smart tag function is not updating the Parameter. I had to manually edit the source code in order to get it to work as above. Are there other areas that Visual Studio does not via Design view update the source code side of things?

Regards X

|||

Hi exup,

There are a lot of demos showing how complex web pages can be done without digging into code. The problem is that, unless you want your pages to perform exactly like the demos, you will need to dig into code.

"Teach yourself asp.net in 24 hours" as a book title sets up a somewhat unrealistic expectation. Asp.net is complicated, and needs time to master. I'd give yourself a few months to get comfortable with all of the quirks.

John

convert the rows into column

I want to convert the row value as column name

example:-->

value ratio

3 4.16666666666
1 1.31578947368
0 0
0 0

To :->

value ratio ratio1 ratio2 ratio3

3 4.166 1.315 0 0

any ideas?

If you are running sql server 2005 you can do it with a pivot table. If sql 2000, I think you have to code it yourself. See

http://jdixon.dotnetdevelopersjournal.com/pivot_table_data_in_sql_server_2000_and_2005.htm

http://www.sqlservercentral.com/articles/Advanced+Querying/pivottableformicrosoftsqlserver/2434/ (registration required)

http://sqlserver2000.databases.aspfaq.com/how-do-i-create-a-cross-tab-or-pivot-query.html

Convert table using DTS

Hi,
how to:
using dts convert row in table to column.
i have table:
col1,col2
b1 , 1
b2 , 2
b1 , 4
b3 , 3
b2 , 5
and i want using dts convert/rewrite this table to another table:
b1,b2,b3
1,null,null
null,2,null
4,null,null
null,null,3
null,5,null"tolo" <ultrapack@.ultrapack.pl> wrote in message news:<4120536f$1@.news.home.net.pl>...
> Hi,
> how to:
> using dts convert row in table to column.
> i have table:
> col1,col2
> b1 , 1
> b2 , 2
> b1 , 4
> b3 , 3
> b2 , 5
> and i want using dts convert/rewrite this table to another table:
> b1,b2,b3
> 1,null,null
> null,2,null
> 4,null,null
> null,null,3
> null,5,null

The easiest way is probably to use a TSQL query:

select
case when col1 = 'b1' then col2 else NULL end as 'b1',
case when col1 = 'b2' then col2 else NULL end as 'b2',
case when col1 = 'b3' then col2 else NULL end as 'b3'
from
dbo.MyTable

You could use this either in an Execute SQL task, or as the source for
a Transform Data task. If both databases are on the same server, or on
different servers on a well-connected network, then you could also do
a simple INSERT...SELECT...

Simon

Convert row with text field to column

Hi experts;

How can I generate the result using typical SQL statement based on the following tables?

Table a - Salesman (salesId, Name) pk : salesId

Table b - Invoice(InvoiceNo, salesId, InvoiceAmt) pk : invoiceNo fk : salesId -> Table a

The result set :

salesId, Name, sum(InvoiceAmt), InvoiceNos with comma separator

For example:

Table a

SalesId Name

S001 Peter

S002 Alice

Table b

InvoiceNo SalesId InvoiceAmt

INV001 S001 $100

INV002 S001 $100

INV003 S001 $400

INV004 S002 $200

Result set

SalesId Name Sum(InvoiceAmt) InvoiceNos

S001 Peter $600 INV001, INV002, INV003

S002 Alice $200 INV004

Thanks

Hi,

You can use the COALESCE function in order to get columns of more than one row as a string expression.

You can find samples at below links.

http://www.kodyaz.com/articles/article.aspx?articleid=29
http://www.kodyaz.com/forums/thread/76.aspx

It is better to create a user defined function which uses the Coalesce and then call this function in your select queries to get related list of invoice no's as one string seperated with commas.

A sample function may be like the below one.

CREATE FUNCTION udf_GetFileList

(

@.RelatedPKID int

)

RETURNS nvarchar(4000)

AS

BEGIN

Declare @.Files nvarchar(4000)

SELECT

@.Files = COALESCE(@.Files + ' ' ,'') + SystemFileName + ','

FROM

AttachedFiles

WHERE

RelatedPKID = @.RelatedPKID

RETURN @.Files

END

GO

I hope this helps.

Eralper

http://www.kodyaz.com

|||

Check out the techniques in this article:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

The "select @.variable = @.variable + columnValue" approach has its dangerous limitations, and should be avoided if possible (It is a neat trick that I have used too, so I am not trying to moralize, just note the issues :)

In 2005, however, there is a much better way of doing things using the XML, functionality, which is outlined in that article.

|||

Hi Eralper;

Thank you. It worked.

You see my SQL as follows:

create function get_invlist
( @.SalesID NvarChar(10))
Returns nvarchar(4000)
as begin

declare @.inv NvarChar(4000);
select @.inv=COALESCE(@.inv + '','')+InvoiceId +',' from sales, invoice
where
sales.salesid = invoice.salesid
and sales.salesid = @.SalesID
Return @.inv
end
go

end function

select sales.salesid, salesName, sum(invamt), test.dbo.get_invlist(sales.salesid)
from sales, invoice
where
sales.salesid = invoice.salesid
group by sales.salesid, salesname;

Result

S0001 Peter 600.00 INV001 ,INV002 ,INV003 ,
S0002 Alice 500.00 INV004 ,

Regards.

|||

Hi Louis;

I agreed you that using @.variable will have limitations. I will test your suggestion later.

Could I use classical SQL to implement this kind of requirement? (i.e. not use @.variable or XML or new function)

Thanks

|||

Hi Louis;

I tried your method. It is better than before.

Please see my SQL.

select sales.salesid, salesName,
Replace((select rtrim(InvoiceId)
as "data()"
from invoice
where
sales.salesid = invoice.salesid
for XML PATH ('')), ' ', ',')
as "@.InvID"
from sales
group by sales.salesid, salesname;

It is so dynamic and easy to understand.

Thanks.

|||I wish it was my method :) It was Aaron Bertrand's. Glad it helped.

convert row to column?

Hi,
I have a row with 53 columns. All but the first three are of the same datatype.
I want to count the number of columns, out of the last 48 in that row, that have the same value. Is there a nifty way to do this with SQL or T-SQL?

Sample row:

a,b,c,1,1,1,2,1,2,2,2,3,3,3,4,4, etc

The values are not static. I want toknow how many 1's how many 2's etc.

Of course within the app I could for example put the row in a .net datarow and loop through the columns.

Thanks,
B.

my first thought was to insert all the column values into a temp table then do a select distinct on it. not very efficient though|||

The real issue is how you want the data returned. Normally, you'd want something like a data row for every a,b,c, followed, by the number, followed by the count of that number. For your example, you would have 4 rows (or more):
a,b,c,1,3
a,b,c,2,4
a,b,c,3,3
a,b,c,4,2

The old fashioned way would be to create a monster statement like:

SELECT col1,col2,col3,col4
UNION ALL
SELECT col1,col2,col3,col5
UNION ALL
SELECT col1,col2,col3,col6
...

Once you have that done (all 48 UNIONED), then you can play with it:

SELECT col1,col2,col3,col4,COUNT(*) as [Count]
FROM ( {Insert the SELECT/UNION/SELECT/UNION Here} ) t1
GROUP BY col1,col2,col3,col4

You can possibly avoid some of this by using the new UNPIVOT clause as well, but I'm not familiar enough to rattle it's syntax off the top of my head.

|||

Here is unpivot solution:

SELECT

col1, col2, col3, cols,count(cols)as myColCountFROM

(

select

col1, col2, col3, myCol, colsFROM

(

SELECT col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24,

col25

, col26, col27, col28, col29, col30, col31, col32, col33, col34, col35, col36, col37, col38, col39, col40, col41, col42, col43, col44, col45, col46, col47, col48, col49, col50, col51, col52, col53

FROM

onerow2) p

UNPIVOT

(colsFOR myColIN

(

col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24,

col25

, col26, col27, col28, col29, col30, col31, col32, col33, col34, col35, col36, col37, col38, col39, col40, col41, col42, col43, col44, col45, col46, col47, col48, col49, col50, col51, col52, col53

)

)

AS unPvt)as myNewTable

GROUP

BY col1, col2, col3, cols

Limno

|||

Thanks for the reply

The values after the first 3 (a,b,c) are user preferences. I need something like :

Adam, 4, 13
Adam, 3, 7
Adam, 2, 16
Adam, 1, 12

to know that Adam has 13 4's 7 3's etc.

I'll try the monster union statement and the UNPIVOT

Currently I'm inserting in a temptable

|||

Thanks

That's what I'm using now but trying to improve efficiency

Convert row data into Column [Didnt get the exact result......]

Hi members

I got the result set as shown below (By executing another query i got this).

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks but this is not the required query. I know anout that but i require the row data will group as a column. I dont wana specify column name.

Please reply

|||If you're using SQL Server 2005 you can use the PIVOT command, but you still have to specify the columns to be listed in the IN clause.|||

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go

In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

|||

I am not getting the out put i require , Again you are using hardcoding stuff. I know the Data in table but , as i cant specify them..... Becuase they are also coming from different table. Please help me. i am stucked.....

The query sould be running on sql and oracle both

|||

What you then could do is to query for a table as the following

Month January February March (...)
=======================================
O 2 10 100
-
I 15 22 32
-
S 100 45 3
-

an afterwards pivoting it with the appropiate function mentioned. This works cause there won′t be more than 12 month as far as someone invents one :-)

But the thing that you have to both support Oracle and SQL Server for the query won′t work with this special functions, because although TSQL and PLSQL have some similar syntax components they differ in specialized ones.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Nope, this is not desire result, plz help me out.................|||I know that this is not the result as you need it, that is where the PIVOT command would take care of, but unless you don′t have any operator like that in Oracle (can′t remember if there is any operator which can do this) you can′t do crossuse this query.

-Jens Suessmeyer.|||help me out , by the result require , either for SQL or PL SQL.........|||

Don't know if you have gotten your answer as of yet, but if not, post some example tables, with create statements, some insert statements for the data, what your exact requirements are and perhaps your "dream" syntax that would make you happy (we'll try to match it) and the exact output.

No guarantees, but it will make sure we are all on the same page...

|||

Louis Davidson wrote:

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go

In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

This is the correct answer = Cross Tab Query

You want to use CASE WHEN ... THEN ... ELSE ... END AS []

Adamus

|||

I guess, the threader hate the hardcoding stuff like "I","S",or so, cause there might be other unposted status value.

Here I post one dynamic SQL aiming to resolve the uncertainty of status value.

Regards.

--create table
create table Raw_Table
(
RecID bigint identity(1,1) not null,
RecDate smalldatetime not null,
Status varchar(1) not null,
Constraint PK_Raw_Table primary key (RecID)
)
--insert data, Repeat following T-Sql with different 'Status' Value for several times
declare @.d datetime
set @.d='2006-07-10'
declare @.mtimes int
set @.mtimes=0
while(@.mtimes<453)
begin
insert into Raw_Table(RecDate,Status)
values(@.d,'K')
set @.mtimes=@.mtimes+1
end
--Aggregate Raw_Table
select datename(month,recdate) as [month],Status,count(*)
as [count]
into ##tempresult
from Raw_Table
group by datename(month,recdate),Status
order by datename(month,recdate)

--Dynamic SQL
declare @.Dsql nvarchar(4000)
declare @.VLastStatus varchar(1)
declare @.VNextStatus varchar(1)

--declare cursor
declare MyCurs Cursor for
select distinct Status from ##tempresult

open MyCurs
fetch next from MyCurs into @.VNextStatus
set @.VLastStatus=''
while @.@.Fetch_Status=0
begin
--this if makes the initial comb Result
if not exists (select * from tempdb.dbo.sysobjects where [name]='##TempResult_Comb_')
begin
set @.Dsql = 'select distinct month into ##TempResult_Comb_ from ##TempResult'
exec sp_executeSql @.Dsql
end
--this @.dsql makes each status table
set @.Dsql=
'select Month ,status,count into ##TempResult_'
+ @.VNextStatus
+' from ##tempresult where Status='
+''''+@.VNextStatus+''''
exec sp_executeSql @.Dsql

--this dsql combined each status table and last comb table
set @.Dsql= 'select a.*,b.count as '+@.VNextStatus+' into ##tempResult_Comb_'+
@.VNextStatus+' from ##TempResult_Comb_'+@.VLastStatus+' as a full join ##TempResult_'+@.VNextStatus+' as b
on a.month=b.month'
exec sp_executeSql @.Dsql

set @.VLastStatus=@.VNextStatus
fetch next from MyCurs into @.VNextStatus
end
close MyCurs
Deallocate MyCurs
--this @.Dsql Select Final Result
set @.Dsql='select * from ##tempResult_Comb_'+@.VLastStatus
exec sp_executeSql @.Dsql

Convert row data into Column

Hi members

I got the result set as shown below (By executing another query i got this).

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks but this is not the required query. I know anout that but i require the row data will group as a column. I dont wana specify column name.

Please reply

|||If you're using SQL Server 2005 you can use the PIVOT command, but you still have to specify the columns to be listed in the IN clause.|||

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go

In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

|||

I am not getting the out put i require , Again you are using hardcoding stuff. I know the Data in table but , as i cant specify them..... Becuase they are also coming from different table. Please help me. i am stucked.....

The query sould be running on sql and oracle both

|||

What you then could do is to query for a table as the following

Month January February March (...)
=======================================
O 2 10 100
-
I 15 22 32
-
S 100 45 3
-

an afterwards pivoting it with the appropiate function mentioned. This works cause there won′t be more than 12 month as far as someone invents one :-)

But the thing that you have to both support Oracle and SQL Server for the query won′t work with this special functions, because although TSQL and PLSQL have some similar syntax components they differ in specialized ones.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Nope, this is not desire result, plz help me out.................|||I know that this is not the result as you need it, that is where the PIVOT command would take care of, but unless you don′t have any operator like that in Oracle (can′t remember if there is any operator which can do this) you can′t do crossuse this query.

-Jens Suessmeyer.|||help me out , by the result require , either for SQL or PL SQL.........|||

Don't know if you have gotten your answer as of yet, but if not, post some example tables, with create statements, some insert statements for the data, what your exact requirements are and perhaps your "dream" syntax that would make you happy (we'll try to match it) and the exact output.

No guarantees, but it will make sure we are all on the same page...

|||

Louis Davidson wrote:

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go

In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

This is the correct answer = Cross Tab Query

You want to use CASE WHEN ... THEN ... ELSE ... END AS []

Adamus

|||

I guess, the threader hate the hardcoding stuff like "I","S",or so, cause there might be other unposted status value.

Here I post one dynamic SQL aiming to resolve the uncertainty of status value.

Regards.

--create table
create table Raw_Table
(
RecID bigint identity(1,1) not null,
RecDate smalldatetime not null,
Status varchar(1) not null,
Constraint PK_Raw_Table primary key (RecID)
)
--insert data, Repeat following T-Sql with different 'Status' Value for several times
declare @.d datetime
set @.d='2006-07-10'
declare @.mtimes int
set @.mtimes=0
while(@.mtimes<453)
begin
insert into Raw_Table(RecDate,Status)
values(@.d,'K')
set @.mtimes=@.mtimes+1
end
--Aggregate Raw_Table
select datename(month,recdate) as [month],Status,count(*)
as [count]
into ##tempresult
from Raw_Table
group by datename(month,recdate),Status
order by datename(month,recdate)

--Dynamic SQL
declare @.Dsql nvarchar(4000)
declare @.VLastStatus varchar(1)
declare @.VNextStatus varchar(1)

--declare cursor
declare MyCurs Cursor for
select distinct Status from ##tempresult

open MyCurs
fetch next from MyCurs into @.VNextStatus
set @.VLastStatus=''
while @.@.Fetch_Status=0
begin
--this if makes the initial comb Result
if not exists (select * from tempdb.dbo.sysobjects where [name]='##TempResult_Comb_')
begin
set @.Dsql = 'select distinct month into ##TempResult_Comb_ from ##TempResult'
exec sp_executeSql @.Dsql
end
--this @.dsql makes each status table
set @.Dsql=
'select Month ,status,count into ##TempResult_'
+ @.VNextStatus
+' from ##tempresult where Status='
+''''+@.VNextStatus+''''
exec sp_executeSql @.Dsql

--this dsql combined each status table and last comb table
set @.Dsql= 'select a.*,b.count as '+@.VNextStatus+' into ##tempResult_Comb_'+
@.VNextStatus+' from ##TempResult_Comb_'+@.VLastStatus+' as a full join ##TempResult_'+@.VNextStatus+' as b
on a.month=b.month'
exec sp_executeSql @.Dsql

set @.VLastStatus=@.VNextStatus
fetch next from MyCurs into @.VNextStatus
end
close MyCurs
Deallocate MyCurs
--this @.Dsql Select Final Result
set @.Dsql='select * from ##tempResult_Comb_'+@.VLastStatus
exec sp_executeSql @.Dsql

Convert row data into Column

Hi members

I got the result set as shown below (By executing another query i got this).

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks but this is not the required query. I know anout that but i require the row data will group as a column. I dont wana specify column name.

Please reply

|||If you're using SQL Server 2005 you can use the PIVOT command, but you still have to specify the columns to be listed in the IN clause.|||

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go

In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

|||

I am not getting the out put i require , Again you are using hardcoding stuff. I know the Data in table but , as i cant specify them..... Becuase they are also coming from different table. Please help me. i am stucked.....

The query sould be running on sql and oracle both

|||

What you then could do is to query for a table as the following

Month January February March (...)
=======================================
O 2 10 100
-
I 15 22 32
-
S 100 45 3
-

an afterwards pivoting it with the appropiate function mentioned. This works cause there won′t be more than 12 month as far as someone invents one :-)

But the thing that you have to both support Oracle and SQL Server for the query won′t work with this special functions, because although TSQL and PLSQL have some similar syntax components they differ in specialized ones.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Nope, this is not desire result, plz help me out.................|||I know that this is not the result as you need it, that is where the PIVOT command would take care of, but unless you don′t have any operator like that in Oracle (can′t remember if there is any operator which can do this) you can′t do crossuse this query.

-Jens Suessmeyer.|||help me out , by the result require , either for SQL or PL SQL.........|||

Don't know if you have gotten your answer as of yet, but if not, post some example tables, with create statements, some insert statements for the data, what your exact requirements are and perhaps your "dream" syntax that would make you happy (we'll try to match it) and the exact output.

No guarantees, but it will make sure we are all on the same page...

|||

Louis Davidson wrote:

You will have to use dynamic SQL to do this without specifying column names. It isn't too awful hard, you just have to use the values you want to pivot on to build the second bits:

Select Month
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH

create table someTable
(
month char(3),
status char(1),
count int
)

insert into someTable (month, status, count)
select 'jan','I',10
union all
select 'jan','O',12
union all
select 'jan','S',22
union all
select 'feb','I',10
union all
select 'apr','O',12
union all
select 'apr','S',22
go
Select Month,
SUM(CASE WHEN Status = 'I' THEN Count END) AS I,
SUM(CASE WHEN Status = 'O' THEN Count END) AS O,
SUM(CASE WHEN Status = 'S' THEN Count END) AS S
FROM SomeTable
GROUP BY MONTH
go

In 2005, you can do something like:

declare @.query varchar(8000)
select @.query = 'select month ' + (
SELECT distinct
',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' +
status + ']' AS [text()]
FROM
sometable s
FOR XML PATH('') ) + ' from SomeTable group by month'

select @.query
exec (@.query)

or in 2000, you can use a slightly less favorable solution:

declare @.query varchar(8000)
select @.query = ''
select @.query = @.query + ',SUM(CASE WHEN Status = ''' + status + ''' THEN Count END) AS [' + status + ']'
from (select distinct status from someTable) as someTable

select @.query = 'select month ' + @.query + ' from SomeTable group by month'
exec (@.query)

Can't put distinct in the @.query bit because it gives wierd results. The @.query = @.query +
thing has some known issues when sorting is required

For more on why the 2005 solution is best: http://www.aspfaq.com/show.asp?id=2529

This is the correct answer = Cross Tab Query

You want to use CASE WHEN ... THEN ... ELSE ... END AS []

Adamus

|||

I guess, the threader hate the hardcoding stuff like "I","S",or so, cause there might be other unposted status value.

Here I post one dynamic SQL aiming to resolve the uncertainty of status value.

Regards.

--create table
create table Raw_Table
(
RecID bigint identity(1,1) not null,
RecDate smalldatetime not null,
Status varchar(1) not null,
Constraint PK_Raw_Table primary key (RecID)
)
--insert data, Repeat following T-Sql with different 'Status' Value for several times
declare @.d datetime
set @.d='2006-07-10'
declare @.mtimes int
set @.mtimes=0
while(@.mtimes<453)
begin
insert into Raw_Table(RecDate,Status)
values(@.d,'K')
set @.mtimes=@.mtimes+1
end
--Aggregate Raw_Table
select datename(month,recdate) as [month],Status,count(*)
as [count]
into ##tempresult
from Raw_Table
group by datename(month,recdate),Status
order by datename(month,recdate)

--Dynamic SQL
declare @.Dsql nvarchar(4000)
declare @.VLastStatus varchar(1)
declare @.VNextStatus varchar(1)

--declare cursor
declare MyCurs Cursor for
select distinct Status from ##tempresult

open MyCurs
fetch next from MyCurs into @.VNextStatus
set @.VLastStatus=''
while @.@.Fetch_Status=0
begin
--this if makes the initial comb Result
if not exists (select * from tempdb.dbo.sysobjects where [name]='##TempResult_Comb_')
begin
set @.Dsql = 'select distinct month into ##TempResult_Comb_ from ##TempResult'
exec sp_executeSql @.Dsql
end
--this @.dsql makes each status table
set @.Dsql=
'select Month ,status,count into ##TempResult_'
+ @.VNextStatus
+' from ##tempresult where Status='
+''''+@.VNextStatus+''''
exec sp_executeSql @.Dsql

--this dsql combined each status table and last comb table
set @.Dsql= 'select a.*,b.count as '+@.VNextStatus+' into ##tempResult_Comb_'+
@.VNextStatus+' from ##TempResult_Comb_'+@.VLastStatus+' as a full join ##TempResult_'+@.VNextStatus+' as b
on a.month=b.month'
exec sp_executeSql @.Dsql

set @.VLastStatus=@.VNextStatus
fetch next from MyCurs into @.VNextStatus
end
close MyCurs
Deallocate MyCurs
--this @.Dsql Select Final Result
set @.Dsql='select * from ##tempResult_Comb_'+@.VLastStatus
exec sp_executeSql @.Dsql

Convert columnar data into rows

I have a table of data that needs to be converted to rows but with a way to identify for each row the column the row value came from.

Here's an example table:

Employee

FED

MEDI

SOCSEC

123

$100

$25

$70

234

$242

$54

$90

345

$75

$10

$55

Here's the format the data needs to be in after it's converted.

Employee

CodeType

Code

Amount

123

T

FED

$100

123

T

MEDI

$25

123

T

SOCSEC

$70

234

T

FED

$242

234

T

MEDI

$54

234

T

SOCSEC

$90

345

T

FED

$75

345

T

MEDI

$10

345

T

SOCSEC

$55

The CodeType will always be T.

This seems similar to some of the transpose-type samples but different enough (for me) to ask about.

Thank you for your help.

DECLARE @.Employee TABLE
(Employee int,
FED decimal(9,2),
MEDI decimal(9,2),
SOCSEC decimal(9,2))
INSERT @.Employee
SELECT 123, 100, 25, 70 UNION ALL
SELECT 234, 242, 54, 90 UNION ALL
SELECT 345, 75, 10, 55

SELECT Employee, 'T' AS CodeType, 'FED' AS Code, FED AS Amount
FROM @.Employee
UNION ALL
SELECT Employee, 'T' AS CodeType, 'MEDI' AS Code, MEDI AS Amount
FROM @.Employee
UNION ALL
SELECT Employee, 'T' AS CodeType, 'SOCSEC' AS Code, SOCSEC AS Amount
FROM @.Employee
ORDER BY Employee, Code

|||

You can do below:

select t.Employee

, 'T' as CodeType

, case c.n

when 1 then 'FED'

when 2 then 'MEDI'

when 3 then 'SOCSEC'

end as Code

, case c.n

when 1 then t.FED

when 2 then t.MEDI

when 3 then t.SOCSEC

end as Amount

from t

cross join (select 1 union all select 2 union all select 3) as c(n)

|||Thank you for your help. It is somewhat similar to the approach I took. The values were for illustration and will be much greater in quantity than the sample data I provided.

What I wound up doing was a series of select statements with a union all between them.|||Thank you for your answer. I'd like to use this but I'm a little lost on the case c.n portion and how that would be changed to fit the actual table and field names.|||

It's just creating a result set that is a single column named n with three rows holding the values 1, 2, and 3. Then you join to that result set with a cross join meaning you get three copies of each of your original rows matched with 1, 2, and 3 respectively. Then it uses that to determine which of the amounts and code types to use. In the example I gave you before it would be like this

DECLARE @.Counter int
SET @.Counter = 1
DECLARE @.Employee TABLE
(Employee int,
FED decimal(9,2),
MEDI decimal(9,2),
SOCSEC decimal(9,2))
INSERT @.Employee
SELECT 123, 100, 25, 70 UNION ALL
SELECT 234, 242, 54, 90 UNION ALL
SELECT 345, 75, 10, 55

select t.Employee
, 'T' as CodeType
, case c.n
when 1 then 'FED'
when 2 then 'MEDI'
when 3 then 'SOCSEC'
end as Code
, case c.n
when 1 then t.FED
when 2 then t.MEDI
when 3 then t.SOCSEC
end as Amount
from @.Employee t
cross join (select 1 union all select 2 union all select 3) as c(n)

Note that the as c(n) in the query is just assigning the column name that you use in the join, the last line of the query is exactly equivalent to

cross join (select 1 as n union all select 2 union all select 3) as c

Convert column values into row

I have a table which stores information in this way:
Date EMP_ID ADCode Amount
-- -- --
--
1-Jan-2007 101 RPF 150
1-Jan-2007 101 RPF.ADV 200
1-Jan-2007 101 GIS 60
1-Jan-2007 101 GIS.ADV 100
1-Jan-2007 102 RPF 50
1-Jan-2007 102 RPF.ADV 100
Using SQL query or any other way, I want to show like this:
Date Emp_ID RPF RPF.ADV GIS GIS.ADV
-----
1-Jan-2007 101 150 200 60
100
1-Jab-2007 102 50 100
0 0In SQL Server 2005 you can use PIVOT operator:
SELECT
Date,
EMP_ID,
ISNULL([RPF],0) [RPF],
ISNULL([RPF.ADV],0) [RPF.ADV],
ISNULL([GIS],0) [GIS],
ISNULL([GIS.ADV],0) [GIS.ADV]
FROM (
SELECT Date, EMP_ID, ADCode, Amount
FROM YourTable
) AS T
PIVOT
(
SUM(T.Amount)
FOR ADCode IN ([RPF], [RPF.ADV], [GIS], [GIS.ADV])
) AS P
In SQL Server 2000 you can use CASE statement:
SELECT
Date,
EMP_ID,
SUM(CASE ADCode WHEN 'RPF' THEN Amount ELSE 0 END) [RPF],
SUM(CASE ADCode WHEN 'RPF.ADV' THEN Amount ELSE 0 END) [RPF.ADV],
SUM(CASE ADCode WHEN 'GIS' THEN Amount ELSE 0 END) [GIS],
SUM(CASE ADCode WHEN 'GIS.ADV' THEN Amount ELSE 0 END) [GIS.ADV]
FROM YourTable
GROUP BY Date, EMP_ID
ORDER BY Date, EMP_ID
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik "RP" <rpk.general@.gmail.com> napisal w wiadomosci
news:1187845549.489646.66260@.q3g2000prf.googlegroups.com...
>I have a table which stores information in this way:
> Date EMP_ID ADCode Amount
> -- -- --
> --
> 1-Jan-2007 101 RPF 150
> 1-Jan-2007 101 RPF.ADV 200
> 1-Jan-2007 101 GIS 60
> 1-Jan-2007 101 GIS.ADV 100
> 1-Jan-2007 102 RPF 50
> 1-Jan-2007 102 RPF.ADV 100
>
> Using SQL query or any other way, I want to show like this:
> Date Emp_ID RPF RPF.ADV GIS GIS.ADV
> -----
> 1-Jan-2007 101 150 200 60
> 100
> 1-Jab-2007 102 50 100
> 0 0
>