How to transpose rows to columns in SQL Server

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)

How to transpose rows to columns in SQL Server

Author

Topic

baalamurugeesant
Starting Member

2 Posts

Posted - 2008-04-23 : 02:19:04

Hi,

I'm facing some problem in Transposing the Rows into Columns in SQL Server 2000.
I have a table with 3 fields: PID, Qty, Price


ID Qty Price
1  12   100
1  24    90
1  36    80
2  13   120
2  26   100

I'd like to transform this table into as follows:


ID 12    13   24   26   36
1  100   N    90   N    80
2  N     120  N    100  N

N = > Null

After transposing the table like this, I can do the operations easily. How to resolve this, Please help me.

Thanks in Advance...!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-23 : 03:45:05

If your column values are static, you can do like this

SELECT ID,
MAX(CASE WHEN Qty=12 THEN Price ELSE NULL END) AS [12],
MAX(CASE WHEN Qty=13 THEN Price ELSE NULL END) AS [13],
MAX(CASE WHEN Qty=24 THEN Price ELSE NULL END) AS [24],
MAX(CASE WHEN Qty=26 THEN Price ELSE NULL END) AS [26],
MAX(CASE WHEN Qty=36 THEN Price ELSE NULL END) AS [36]
FROM YourTable
GROUP BY ID

baalamurugeesant
Starting Member

2 Posts

Posted - 2008-05-09 : 02:27:01

Thanks Visakh for your reply.
But my column values are not static. It is dynamic. The columns are depends on rows. The total number of distinct rows are converted as columns.

Help me...!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-05-09 : 08:54:46

On 2000, this is the way I've been using to pivot tables like this. (using you table guide for data).

This only works if you have unique QTY and Price info (you don't have duplicate qty 12 for instance..

Try it and see.


IF object_Id('tempdb..#prePivot') IS NOT NULL DROP TABLE #prePivot
IF object_Id('tempdb..#results') IS NOT NULL DROP TABLE #results

CREATE TABLE #prePivot (
	[ID] INT
	, [Qty] INT
	, [Price] MONEY
	)

CREATE TABLE #results (
	[ID] INT
	)

INSERT INTO #prePivot
SELECT 1, 12, 100
UNION SELECT 1, 24, 90
UNION SELECT 1, 36, 80
UNION SELECT 2, 13, 120
UNION SELECT 2, 26, 100

DECLARE @sql VARCHAR(8000)
DECLARE @pivot INT

DECLARE pivotCursor CURSOR LOCAL READ_ONLY FOR
SELECT DISTINCT
	[Qty]
FROM
	#prePivot

OPEN pivotCursor
	FETCH NEXT FROM pivotCursor INTO @pivot

	WHILE (@@FETCH_STATUS = 0) BEGIN

		SET @sql = '
		ALTER TABLE #results ADD [' + CAST(@pivot AS VARCHAR(50)) + '] INT'
		EXEC (@sql)

		FETCH NEXT FROM pivotCursor INTO @pivot
	END
CLOSE pivotCursor

INSERT INTO #results ([ID])
SELECT DISTINCT [ID] FROM #prePivot

OPEN pivotCursor
	FETCH NEXT FROM pivotCursor INTO @pivot

	WHILE (@@FETCH_STATUS = 0) BEGIN

		SET @sql = '
		UPDATE #results
		SET
			[' + CAST(@pivot AS VARCHAR(50)) + '] = pp.[Price]
		FROM
			#prePivot pp
		WHERE
			pp.[Qty] = ' + CAST(@pivot AS VARCHAR(50)) + '
			AND pp.[ID] = #results.[ID]'

		EXEC (@sql)

		FETCH NEXT FROM pivotCursor INTO @pivot
	END
CLOSE pivotCursor
DEALLOCATE pivotCursor

SELECT * FROM #results

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources