?
Solved

Pivot (Cross-tab query) in T-SQL

Posted on 2003-03-26
8
Medium Priority
?
2,404 Views
Last Modified: 2010-04-15
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
Comment
Question by:batdan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 8212240
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
 
LVL 1

Expert Comment

by:dmeili
ID: 8212288
0
 
LVL 3

Expert Comment

by:keystrokes
ID: 8214094
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:batdan
ID: 8218102
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 8218835
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
 

Expert Comment

by:CleanupPing
ID: 9275516
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
 
LVL 1

Author Comment

by:batdan
ID: 9730778
Sorry for the extreme delay in accepting this answer, and thanks for all the help.
0
 
LVL 2

Expert Comment

by:MCITP
ID: 30831665
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question