SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 trying to figure out pivots in sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sambosley
Starting Member

USA
3 Posts

Posted - 01/20/2014 :  12:43:09  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/21/2014 :  09:29:30  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sambosley
Starting Member

USA
3 Posts

Posted - 01/21/2014 :  12:25:32  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/22/2014 :  06:53:14  Show Profile  Reply with Quote
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
Go to Top of Page

sambosley
Starting Member

USA
3 Posts

Posted - 01/24/2014 :  14:53:12  Show Profile  Reply with Quote
That worked, thank you very much

Samuel Bosley
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/27/2014 :  07:29:32  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000