[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2416
  • Last Modified:

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
0
batdan
Asked:
batdan
1 Solution
 
LowfatspreadCommented:
I think the following should work, and construct for you
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
0
 
keystrokesCommented:
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)
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
batdanAuthor Commented:
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.
0
 
LowfatspreadCommented:
I think I've corrected the proc for your varchar ,
any character... requirements....

I assume you mean the actual generated statement
is taking a long time to run...
can you show me what was generated...
how much data is on your table?
have your considered specifying a date range?

Declare @sql varchar(8000)
Declare @startid varchar(n) -- N should twice the size of the actual id column
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 ''' + Replace([id],'''','''''') + ''' then 1 else 0 end) as [' + [id] + ']', @startid=[id]
from table1 where [id] > @startid
end

set @sql=@sql + ' from table1 group by [date]'

--Print 'SQL is '
--Print @Sql
--Print '----'

Exec (@sql)





0
 
CleanupPingCommented:
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.
0
 
batdanAuthor Commented:
Sorry for the extreme delay in accepting this answer, and thanks for all the help.
0
 
MCITPCommented:
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now