batdan
asked on
Pivot (Cross-tab query) in T-SQL
Hi.
I have a simple table looking something like the following:
Date ID Value
--------------------------
21/03/2003 a 12
21/03/2003 b 54
21/03/2003 c 32
22/03/2003 a 76
22/03/2003 b 34
22/03/2003 c 1
23/03/2003 a 72
23/03/2003 b 42
23/03/2003 c 65
I'm trying to query the data so the result will look like the following:
Date a b c
--------------------------
21/03/2003 12 54 32
22/03/2003 76 34 1
23/03/2003 72 42 65
I've found that I can use the following T-SQL to do this:
SELECT [Date],
SUM(CASE [ID] WHEN 1 THEN [value] ELSE 0 END) AS a,
SUM(CASE [ID] WHEN 2 THEN [value] ELSE 0 END) AS b,
SUM(CASE [ID] WHEN 3 THEN [value] ELSE 0 END) AS c
FROM Table1
GROUP BY [Date]
The only problem with this is that if a record is added for a date with the ID of d or whatever, then I'll need to adjust the query to cope with it. Does anyone know of a generic query that I can use to cope with extra ID's being added with having to alter the T-SQL?
Thanks in advance
I have a simple table looking something like the following:
Date ID Value
--------------------------
21/03/2003 a 12
21/03/2003 b 54
21/03/2003 c 32
22/03/2003 a 76
22/03/2003 b 34
22/03/2003 c 1
23/03/2003 a 72
23/03/2003 b 42
23/03/2003 c 65
I'm trying to query the data so the result will look like the following:
Date a b c
--------------------------
21/03/2003 12 54 32
22/03/2003 76 34 1
23/03/2003 72 42 65
I've found that I can use the following T-SQL to do this:
SELECT [Date],
SUM(CASE [ID] WHEN 1 THEN [value] ELSE 0 END) AS a,
SUM(CASE [ID] WHEN 2 THEN [value] ELSE 0 END) AS b,
SUM(CASE [ID] WHEN 3 THEN [value] ELSE 0 END) AS c
FROM Table1
GROUP BY [Date]
The only problem with this is that if a record is added for a date with the ID of d or whatever, then I'll need to adjust the query to cope with it. Does anyone know of a generic query that I can use to cope with extra ID's being added with having to alter the T-SQL?
Thanks in advance
Well, if you want a script just for this specific table, then do this:
declare @numID int, @i int
@sql varchar(300)
select @numID=[id] from table1 group by [id]
select @i=1
select @sql='SELECT [Date],'
while @i<=@numID
begin
select @sql=@sql+'SUM(CASE [ID] WHEN ' + convert(varchar(2),@i) + ' THEN [value] ELSE 0 END) AS '+char(64+@i)+
','
end
select @sql=left(@sql,len(@sql)-1
select @sql=@sql+' FROM Table1 GROUP BY [Date]'
exec(@sql)
declare @numID int, @i int
@sql varchar(300)
select @numID=[id] from table1 group by [id]
select @i=1
select @sql='SELECT [Date],'
while @i<=@numID
begin
select @sql=@sql+'SUM(CASE [ID] WHEN ' + convert(varchar(2),@i) + ' THEN [value] ELSE 0 END) AS '+char(64+@i)+
','
end
select @sql=left(@sql,len(@sql)-1
select @sql=@sql+' FROM Table1 GROUP BY [Date]'
exec(@sql)
ASKER
Hi, thanks for the replies.
LowFatSpread:
The query executes but I've had to stop it 10 minutes through as it still hasn't returned any records. Is this just a very slow query of could there be something else wrong?
keystrokes:
When I try to run this I'm getting an error which states:
Syntax error converting the varchar value 'a' to a column of data type int.
Also please bear in mind (sorry I should have mentioned this) that the ID field values in the actual table are likely to be a variable length string which could also include spaces and not necessarily ordered.
dmeilli:
Thanks for the link, but this isn't exactly what I was looking for.
Any further help appreciated.
LowFatSpread:
The query executes but I've had to stop it 10 minutes through as it still hasn't returned any records. Is this just a very slow query of could there be something else wrong?
keystrokes:
When I try to run this I'm getting an error which states:
Syntax error converting the varchar value 'a' to a column of data type int.
Also please bear in mind (sorry I should have mentioned this) that the ID field values in the actual table are likely to be a variable length string which could also include spaces and not necessarily ordered.
dmeilli:
Thanks for the link, but this isn't exactly what I was looking for.
Any further help appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
batdan:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
ASKER
Sorry for the extreme delay in accepting this answer, and thanks for all the help.
I am suprised that no one here knew how to build a crosstab query.
here is the syntax for a crossdab query example
SELECT CategoryField, *
FROM
(
SELECT RE.*, B.CategoryField
FROM
Tale RE
INNER JOIN
dbo.OtherTable B
ON RE.RecID= B.RecID
) a
PIVOT
(
SUM(Amount)
FOR SomeFieldYear
IN ([2010],[2011])
)
AS p
here is the syntax for a crossdab query example
SELECT CategoryField, *
FROM
(
SELECT RE.*, B.CategoryField
FROM
Tale RE
INNER JOIN
dbo.OtherTable B
ON RE.RecID= B.RecID
) a
PIVOT
(
SUM(Amount)
FOR SomeFieldYear
IN ([2010],[2011])
)
AS p
the generic case...
Declare @sql varchar(8000)
Declare @startid char(1)
set @startid=' '
Set @sql=' Select [date] '
while exists (select top 1 [id] from table1 where [id] > @startid)
begin
Select top 1 @sql=@sql + ','
'Sum(case [id] when ' + [id] + ' then 1 else 0 end) as [' + [id] + ']', @startid=[id]
from table1 where [id] > @startid
end
set @sql=@sql + ' from table1 group by [date]'
Exec (@sql)
it would probably be better if you established the
query for the set you currently know of (a,b,c)
and then alter the queries to exclude these values as well so that at least you initially only do 1 select
hth