trying to figure out pivots in sql

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

General SQL Server Forums

New to SQL Server Programming

trying to figure out pivots in sql

Author

Topic

sambosley
Starting Member

4 Posts

Posted - 2014-01-20 : 12:43:09

i am trying to figure out how to do this, i have a unique, called PL_ITEM_NO, for every result i want up to the first 5 of this column ALT_COMPT to go horizontally like this.. PL_ITEM_NO,alt_1,Alt_2,Alt_3,Alt_4,Alt_5.

I can get two easily by saying min(ALT_COMPT) as alt_1,max(ALT_COMPT0 as alt_2, but how can i get it to cycle through and get 5 of them.
below is what i am working with.

SELECT T1.PL_ITEM_NO , min(T2.ALT_COMPT) AS ALT_1,max(T2.ALT_COMPT) AS ALT_2

FROM dbo.ext_pln_007_03_data AS T1 LEFT OUTER JOIN
dbo.ext_pln_007_03_data AS T2 ON T1.PL_ITEM_NO = T2.PL_ITEM_NO

GROUP BY T1.PL_ITEM_NO

this yields something like this..
PL_ITEM_NO ALT_1 ALT_2
39538721 248M 248M
37703194 221A 221A
50047599 424A 424A
49030125 325F 327R
43283180 158I 158I

Samuel Bosley

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-21 : 09:29:30

[code]
SELECT PL_ITEM_NO,
[1] AS ALT_1,
[2] AS ALT_2,
[3] AS ALT_3,
[4] AS ALT_4,
[5] AS ALT_5
FROM
(
SELECT T1.PL_ITEM_NO , T2.ALT_COMPT,ROW_NUMBER() OVER (PARTITION BY T1.PL_ITEM_NO ORDER BY T2.ALT_COMPT) AS Seq
FROM dbo.ext_pln_007_03_data AS T1 LEFT OUTER JOIN
dbo.ext_pln_007_03_data AS T2 ON T1.PL_ITEM_NO = T2.PL_ITEM_NO
)t
PIVOT(MAX(ALT_COMPT) FOR Seq IN ([1],[2],[3],[4],[5]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sambosley
Starting Member

4 Posts

Posted - 2014-01-21 : 12:25:32

I looks great, but cant get it to work,

Its not recognizing the function ROW_NUMBER(), its that a stored procedure?

Msg 195, Level 15, State 10, Line 9
'ROW_NUMBER' is not a recognized function name.

Samuel Bosley

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 06:53:14

quote:
Originally posted by sambosley

I looks great, but cant get it to work,

Its not recognizing the function ROW_NUMBER(), its that a stored procedure?

Msg 195, Level 15, State 10, Line 9
'ROW_NUMBER' is not a recognized function name.

Samuel Bosley

Nope..its an inbuilt function available for sql 2005
So are you on SQL 2000? Then my suggestion wont work.
then you need something like this


SELECT T1.PL_ITEM_NO , T2.ALT_COMPT,IDENTITY(int,1,1) AS ID INTO #temp
FROM dbo.ext_pln_007_03_data AS T1 LEFT OUTER JOIN
dbo.ext_pln_007_03_data AS T2 ON T1.PL_ITEM_NO = T2.PL_ITEM_NO

SELECT PL_ITEM_NO,
MAX(CASE WHEN Rn = 1 THEN ALT_COMPT END) AS ALT_1,
MAX(CASE WHEN Rn = 2 THEN ALT_COMPT END) AS ALT_2,
MAX(CASE WHEN Rn = 3 THEN ALT_COMPT END) AS ALT_3,
MAX(CASE WHEN Rn = 4 THEN ALT_COMPT END) AS ALT_4
FROM
(
SELECT PL_ITEM_NO , ALT_COMPT
COALESCE((SELECT COUNT(*) FROM #temp WHERE PL_ITEM_NO = t.PL_ITEM_NO  AND ALT_COMPT < t.ALT_COMPT),0) + 1 AS Rn
FROM #Temp t
)r
GROUP BY PL_ITEM_NO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sambosley
Starting Member

4 Posts

Posted - 2014-01-24 : 14:53:12

That worked, thank you very much

Samuel Bosley

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-27 : 07:29:32

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources