August 26, 2008 at 11:37 pm
hi, my table contain the following values
no name
1 aaa
1 bbb
1 ccc
2 vvv
2 nnn
i need output as following ( max no.of cols is 5 )
no col1 col2 col3 col4 col5
1 aaa bbb ccc null null
2 vvv nnn null null null
please give me some inputs.
August 26, 2008 at 11:41 pm
Hi there,
try reading this
http://msdn.microsoft.com/en-us/library/ms177410.aspx
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 27, 2008 at 12:05 am
i do not need any group by /sum values here. can you provide me a simple pivot query based on my input
August 27, 2008 at 5:29 am
Here is one possible solution:
[font="Courier New"]-- no col1 col2 col3 col4 col5
-- 1 aaa bbb ccc null null
-- 2 vvv nnn null null null
/*
CREATE TABLE #tmpTest (GroupNum INT, Val VARCHAR(3))
INSERT #tmpTest VALUES (1,'aaa')
INSERT #tmpTest VALUES (1,'bbb')
INSERT #tmpTest VALUES (1,'ccc')
INSERT #tmpTest VALUES (2,'vvv')
INSERT #tmpTest VALUES (2,'nnn')
*/
; WITH Data (ColID, GroupNum, Val)
AS (
SELECT RANK() OVER (PARTITION BY GroupNum ORDER BY Val)
, GroupNum, Val
FROM #tmpTest
)
SELECT
D.GroupNum
, (SELECT X.Val FROM Data X WHERE X.GroupNum = D.GroupNum AND X.ColID = 1) AS col1
, (SELECT X.Val FROM Data X WHERE X.GroupNum = D.GroupNum AND X.ColID = 2) AS col2
, (SELECT X.Val FROM Data X WHERE X.GroupNum = D.GroupNum AND X.ColID = 3) AS col3
, (SELECT X.Val FROM Data X WHERE X.GroupNum = D.GroupNum AND X.ColID = 4) AS col4
, (SELECT X.Val FROM Data X WHERE X.GroupNum = D.GroupNum AND X.ColID = 5) AS col5
FROM
Data D
GROUP BY
D.GroupNum[/font]
August 27, 2008 at 5:38 am
it works fine. thanking you very much
August 27, 2008 at 5:55 am
Hi there here is a solution using the PIVOT function
-- no col1 col2 col3 col4 col5
-- 1 aaa bbb ccc null null
-- 2 vvv nnn null null null
/*
CREATE TABLE #tmpTest (GroupNum INT, Val VARCHAR(3))
INSERT #tmpTest VALUES (1,'aaa')
INSERT #tmpTest VALUES (1,'bbb')
INSERT #tmpTest VALUES (1,'ccc')
INSERT #tmpTest VALUES (2,'vvv')
INSERT #tmpTest VALUES (2,'nnn')
*/
SELECT *
FROM
(
SELECT
RANK() OVER (PARTITION BY GroupNum ORDER BY Val) as [RNum],
*
FROM #tmpTest
) t
PIVOT (MIN(Val) FOR [RNum] IN ([1],[2],[3],[4],[5])) PVT
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 27, 2008 at 5:59 am
Here is another possible solution:
[font="Courier New"]-- Create sample data
CREATE TABLE #Table ([no] INT, [name] CHAR(3))
INSERT INTO #Table ([no], [name])
SELECT 1, 'aaa' UNION ALL
SELECT 1, 'bbb' UNION ALL
SELECT 1, 'ccc' UNION ALL
SELECT 2, 'vvv' UNION ALL
SELECT 2, 'nnn'
-- Run the query
SELECT t1.[no], t1.[name] AS Col1, t2.[name] AS Col2, t3.[name] AS Col3, t4.[name] AS Col4, t5.[name] AS Col5
FROM (SELECT [no], MIN([name]) AS [name] FROM #Table GROUP BY [no]) t1
LEFT JOIN #Table t2 ON t2.[no] = t1.[no] AND t2.[name] = (SELECT MIN([name]) FROM #Table WHERE [no] = t1.[no] AND [name] > t1.[name])
LEFT JOIN #Table t3 ON t3.[no] = t1.[no] AND t3.[name] = (SELECT MIN([name]) FROM #Table WHERE [no] = t1.[no] AND [name] > t2.[name])
LEFT JOIN #Table t4 ON t4.[no] = t1.[no] AND t4.[name] = (SELECT MIN([name]) FROM #Table WHERE [no] = t1.[no] AND [name] > t3.[name])
LEFT JOIN #Table t5 ON t5.[no] = t1.[no] AND t5.[name] = (SELECT MIN([name]) FROM #Table WHERE [no] = t1.[no] AND [name] > t4.[name])
-- drop the sample table
DROP TABLE #Table
[/font]
Results:
no Col1 Col2 Col3 Col4 Col5
----------- ---- ---- ---- ---- ----
1 aaa bbb ccc NULL NULL
2 nnn vvv NULL NULL NULL
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 27, 2008 at 5:59 am
Thanking you very much.
August 27, 2008 at 6:15 am
I think the OP at least owes us the work of taking these three solutions and determining which one is fastest.
August 27, 2008 at 6:18 am
Good call it would be great to know which is faster
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 27, 2008 at 6:34 am
HI All,
Not all of the solutions seem to work.
I was going to do some performance testing and noticed that not all of them work.
Here is the code for all the solutions
-- Create sample data
CREATE TABLE #Table ([no] INT, [name] CHAR(3))
INSERT INTO #Table ([no], [name])
SELECT 1, 'aaa' UNION ALL
SELECT 1, 'bbb' UNION ALL
SELECT 1, 'ccc' UNION ALL
SELECT 2, 'vvv' UNION ALL
SELECT 2, 'nnn' UNION ALL
SELECT 3, 'vv' UNION ALL
SELECT 3, 'nn' UNION ALL
SELECT 3, 'vv' UNION ALL
SELECT 4, 'rrr'
SELECT * FROM #Table
SET STATISTICS IO ON
SET STATISTICS TIME ON
--Michael Earl
PRINT 'Michael Earl'
PRINT '------------------------------------------------'
; WITH Data (ColID, [GroupNum], [val])
AS (
SELECT RANK() OVER (PARTITION BY [no] ORDER BY [name])
, [no], [name]
FROM #Table
)
SELECT
D.GroupNum as [no]
, (SELECT X.Val FROM Data X WHERE X.GroupNum = D.GroupNum AND X.ColID = 1) AS col1
, (SELECT X.Val FROM Data X WHERE X.GroupNum = D.GroupNum AND X.ColID = 2) AS col2
, (SELECT X.Val FROM Data X WHERE X.GroupNum = D.GroupNum AND X.ColID = 3) AS col3
, (SELECT X.Val FROM Data X WHERE X.GroupNum = D.GroupNum AND X.ColID = 4) AS col4
, (SELECT X.Val FROM Data X WHERE X.GroupNum = D.GroupNum AND X.ColID = 5) AS col5
FROM
Data D
GROUP BY
D.GroupNum
PRINT '------------------------------------------------'
--Chris Stobbs
PRINT 'Chris Stobbs'
PRINT '------------------------------------------------'
SELECT *
FROM
(
SELECT
RANK() OVER (PARTITION BY [no] ORDER BY [name]) as [RNum],
*
FROM #Table
) t
PIVOT (MIN([name]) FOR [RNum] IN ([1],[2],[3],[4],[5])) PVT
PRINT '------------------------------------------------'
--Chris Morris
PRINT 'Chris Morris'
PRINT '------------------------------------------------'
SELECT t1.[no], t1.[name] AS Col1, t2.[name] AS Col2, t3.[name] AS Col3, t4.[name] AS Col4, t5.[name] AS Col5
FROM (SELECT [no], MIN([name]) AS [name] FROM #Table GROUP BY [no]) t1
LEFT JOIN #Table t2 ON t2.[no] = t1.[no] AND t2.[name] = (SELECT MIN([name]) FROM #Table WHERE [no] = t1.[no] AND [name] > t1.[name])
LEFT JOIN #Table t3 ON t3.[no] = t1.[no] AND t3.[name] = (SELECT MIN([name]) FROM #Table WHERE [no] = t1.[no] AND [name] > t2.[name])
LEFT JOIN #Table t4 ON t4.[no] = t1.[no] AND t4.[name] = (SELECT MIN([name]) FROM #Table WHERE [no] = t1.[no] AND [name] > t3.[name])
LEFT JOIN #Table t5 ON t5.[no] = t1.[no] AND t5.[name] = (SELECT MIN([name]) FROM #Table WHERE [no] = t1.[no] AND [name] > t4.[name])
PRINT '------------------------------------------------'
-- drop the sample table
DROP TABLE #Table
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 27, 2008 at 6:41 am
I noticed that your two solutions don't work if there is a dupicate row, so I'm not sure if that situation will actually occur or not.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 27, 2008 at 6:42 am
Let the OP do some of the work...
August 27, 2008 at 6:43 am
ooops sorry got ahead of myself
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 27, 2008 at 6:44 am
Hi Chris, thanks for pointing this out, though I agree that the OP should do some of the work here! My solution will work where there are dupes in the data if SELECT DISTINCT is used in the outer query.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply