January 24, 2009 at 10:09 pm
drop table #test
go
--===== Create a test table and populate it.
CREATE TABLE #Test
(OrderNo INT, SeqNo INT, Type CHAR(2))
INSERT INTO #Test
(OrderNo, SeqNo, Type)
SELECT 1234,1,'BL' UNION ALL
SELECT 1234,2,'PD' UNION ALL
SELECT 1234,3,'GL' UNION ALL
SELECT 1234,4,'GL' UNION ALL
SELECT 1235,1,'PL' UNION ALL
SELECT 1235,2,'VL'
--===== Display the original content of the table
SELECT OrderNo, SeqNo, Type
FROM #Test
ORDER BY OrderNo, SeqNo
Now this table need to be pivoted as
OrderNo 1 2 3 4
1234 BL PD GL GL
1235 PL VL
Now, again I dont know the range of SeqNo. This is basically for some report purposes.
January 24, 2009 at 11:37 pm
You could use a cursor that steps through each unique SeqNo and dynamically generate your pivot statement, which you would fire with an EXECUTE statement. There are plenty of reasons to frown on such a solution, but it is the only way to accomplish some tasks.
January 25, 2009 at 9:47 pm
Dynamic sql is the only way, although you don't necessarily use cursors.
CREATE TABLE #Test
(OrderNo INT, SeqNo INT, Type CHAR(2))
INSERT INTO #Test
(OrderNo, SeqNo, Type)
SELECT 1234,1,'BL' UNION ALL
SELECT 1234,2,'PD' UNION ALL
SELECT 1234,3,'GL' UNION ALL
SELECT 1234,4,'GL' UNION ALL
SELECT 1235,1,'PL' UNION ALL
SELECT 1235,2,'VL'
go
DECLARE @cols NVARCHAR(2000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + cast(SeqNo as varchar)
FROM #Test AS t2
ORDER BY '],[' + cast(SeqNo as varchar)
FOR XML PATH('')
), 1, 2, '') + ']'
--code in the above line is
--), 1, 2, '') + ']'
DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT OrderNo, '+
@cols +'
FROM
(SELECT OrderNo
, SeqNo
, [Type]
FROM #Test) p
PIVOT
(
MAX([Type])
FOR SeqNo IN
( '+
@cols +' )
) AS pvt
ORDER BY OrderNo;'
exec sp_executesql @query
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
January 25, 2009 at 11:14 pm
Nice script, I look forward to trying it myself!
January 26, 2009 at 11:27 am
You could also try a dynamic cross-tab, as mentioned in the following article:
January 26, 2009 at 3:50 pm
All of the other posts have directed you to a dynamic pivot\cross-tab solution. I would like to point out that most (if not all) reporting tools available will not be able to deal with a resultset that returns varying number of columns based upon the input.
How exactly are you going to design a report that can have three columns the first time it is run, four columns the next time - and 15 columns later on? Report generators (Crystal, SSRS, Cognos, etc...) all need to read the metadata, make the columns available and allow you to build your report.
I see this kind of request all the time - and the best answer is to not do this in the database. All of the major reporting tools have the ability to create cross-tab (matrix) reports. Use those controls instead of trying to build dynamic cross-tabs\pivots in SQL Server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply