Query challenge- Pivot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query challenge- Pivot

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-11-14 : 11:32:17

I have a table like below...


Unit     Treatment      count
A01     CAPD            10
A01     CCPD            11
A01     HD              55
A01     HD              10
A01     HDH              9
A02     CAPD             9
A02     CCPD            11
A02     HD              45
A02     HD               2
A03     HDH             32
A04     HDH              7
A05     HD               9
A06     HD               1
A07     CCPD             3
A07     HD               6
A08     CAPD             8               
A08     CCPD             2               
A08     HD               5               
A08     HDH              3               
A08     HD               1

and I am trying to return the result like below ( pivot).. what is the best way to get the output like below.. can you show me the example?


     HD     HDH     CAPD     CCPD     Total
A01   65     9        10       11        95
A02   47               9       11        67
A03          32                32
A04           7                  7
A05     9                        9
A06     1                        1
A07     6                        3        9
A08     6     3        8         2        19

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-11-14 : 11:33:03

I am using SQL server 2005

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 11:35:07

Then you need to read about PIVOT operator in BOOKS ONLINE.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 11:37:01

SQL2000 way would be

SELECT Unit,
MAX(CASE WHEN Treatment = 'HD' THEN [count] END) AS HD,
MAX(CASE WHEN Treatment = 'HDH' THEN [count] END) AS HDH,
MAX(CASE WHEN Treatment = 'CAPD' THEN [count] END) AS CAPD,
MAX(CASE WHEN Treatment = 'CCPD' THEN [count] END) AS CCPD,
COUNT(*)
from yourtable
group by unit
order by unit

Peter Larsson
Helsingborg, Sweden

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-11-14 : 11:41:35

thank but..

thanks, but how can i grap the treatment value dynamically.. It;s not going to be always HD,HDH,CAPD,and CCPD..

I would like to grap the distinct value of treatment in the table and do the calcuation...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 11:48:55

Read this article. It will suite you.
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Peter Larsson
Helsingborg, Sweden

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-11-14 : 13:00:52

thank you

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-14 : 15:34:36

You can do it with the PIVOT operator in SQL Server 2005 with only a small amount of dynamic code, like this:

DECLARE @pivotColumns nvarchar(2000), @sql nvarchar(4000)
SET @pivotColumns = ''
SELECT @pivotColumns = @pivotColumns + '[' + Treatment + '],'
FROM (SELECT distinct Treatment FROM yourtable) p
SET @pivotColumns = LEFT(@pivotColumns, LEN(@pivotColumns) - 1)
SET @sql = 'SELECT *
FROM 
(SELECT Unit, Treatment, [count]
FROM yourtable) p
PIVOT
(
SUM([count])
FOR Treatment IN
( ' + @pivotColumns + ' )
) AS pvt
ORDER BY Unit'
EXEC (@sql)

See this article for more detail
http://www.theabstractionpoint.com/dynamiccolumns.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-15 : 10:02:51

http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources