Dynamic Pivot/Transposition - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Dynamic Pivot/Transposition

Author

Topic

Tahsin
Starting Member

34 Posts

Posted - 2012-08-07 : 20:04:36

I am looking for a way to take the following table:

id field1 field2 field3 field4 field5 field6 field7
1 1 NULL NULL 1 1 1 1
2 NULL 2 NULL NULL NULL NULL NULL
3 NULL 1 3 NULL NULL 1 NULL
4 2 2 2 2 2 2 2

and transpose it into something like this:

id column_name
1 field1
1 field4
1 field5
1 field6
1 field7
2 field2
2 field2
3 field2
3 field3
3 field3
3 field3
3 field6
4 field1
4 field1
4 field2
4 field2
4 field3
4 field3
4 field4
4 field4
4 field5
4 field5
4 field6
4 field6
4 field7
4 field7

I was able to create a dynamic pivot to transpose the column names, but was having issues with creating multiple column names given the count.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-08 : 07:01:48

You can use the UNPIVOT operator like this:

SELECT
	id,
	fields
FROM
	YourTable
	UNPIVOT
	(
	    fields FOR field IN 
			([field1], [field2], [field3], 
			[field4], [field5], [field6], [field7]
			)
	)u

Tahsin
Starting Member

34 Posts

Posted - 2012-08-08 : 16:07:11

Thanks, I finally figured it out. UNPIVOT is only a start, and you have to combine it with a recursive CTE to get the desired values. Code below:


-- Create Test Table
CREATE TABLE [dbo].[Test](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[field1] [varchar](4) NULL,
	[field2] [varchar](4) NULL,
	[field3] [varchar](4) NULL,
	[field4] [varchar](4) NULL,
	[field5] [varchar](4) NULL,
	[field6] [varchar](4) NULL,
	[field7] [varchar](4) NULL
) ON [PRIMARY]
GO

-- Populate Sample Values
INSERT INTO Test (field1, field2, field3, field4, field5, field6, field7)
VALUES 
	(1,NULL,NULL,1,1,1,1)
	,(NULL,2,NULL,NULL,NULL,NULL,NULL)
	,(NULL,1,3,NULL,NULL,1,NULL)
	,(2,2,2,2,2,2,2)

-- Original Data Set
SELECT * FROM Test

-- Recursive CTE with UNPIVOT
-- UNPIVOT for transposition
-- Recursive CTE for column count expansion	 
;WITH cte AS 
( 
  SELECT ID, 
         Field, 
         Value, 
         1 AS Rec_Iteration 
  FROM (
		SELECT ID, Field, Value
		FROM
			(SELECT ID, field1, field2, field3, field4, field5, field6, field7
			 FROM Test_DELETE2) 
		AS P
		UNPIVOT
			(Value FOR Field IN
				 (field1, field2, field3, field4, field5, field6, field7)
			)AS unpvt
		) U
  --where Value > 1 
  UNION ALL   
  SELECT ID, 
         Field, 
         Value, 
         Rec_Iteration + 1 
  FROM cte  
  WHERE Rec_Iteration < Value 
) 
SELECT ID, 
       Field, 
       Value, 
       Rec_Iteration 
FROM cte 
ORDER BY ID, Field

-- Cleanup/Drop Table
DROP TABLE Test
GO

vijayan.vinu3
Starting Member

19 Posts

Posted - 2012-08-09 : 06:25:06

How about a little simpler....like this:


--Creating Table

Create Table Ex
(id	int, 
 field1	int, 
 field2	int, 
 field3	int, 
 field4	int, 
 field5	int, 
 field6	int, 
 field7 int )


--Inserting Sample Data

Insert Into Ex
Select 1,	1,		NULL,	NULL,	1,		1,		1,		1
Union ALL
Select 2,	NULL,	2,		NULL,	NULL,	NULL,	NULL,	NULL
Union ALL
Select 3,	NULL,	1,		3,		NULL,	NULL,	1,		NULL
Union ALL
Select 4,	2,		2,		2,		2,		2,		2,		2


--Query For Your Requirement

Select Ids, Fields From
(
	 Select [Ids], [Value], [Fields] From Ex
	 Cross Apply(
				Values(id, field1, 'Field1'), 
					  (id, field2, 'Field2'),
					  (id, field3, 'Field3'),
					  (id, field4, 'Field4'),
					  (id, field5, 'Field5'),
					  (id, field6, 'Field6'),
					  (id, field7, 'Field7')
				)A ([Ids], [Value], [Fields])
	 Where Value IS NOT NULL
) As b

Tahsin
Starting Member

34 Posts

Posted - 2012-08-09 : 15:26:14

Vijayan, your query does the transposition nicely, but it doesn't expand the column counts. It will only give 1 instance of the column name per ID, but you can combine this with a recursive CTE instead of using an UNPIVOT, which would be a good alternate solution.

vijayan.vinu3
Starting Member

19 Posts

Posted - 2012-08-10 : 00:08:55

quote:
Originally posted by Tahsin

Vijayan, your query does the transposition nicely, but it doesn't expand the column counts. It will only give 1 instance of the column name per ID, but you can combine this with a recursive CTE instead of using an UNPIVOT, which would be a good alternate solution.

Hmm...yes I missed the logic a little. yes, we would need a recursive CTE to do that...let me see if I can implement one into my solution....would be a nice query if i could implement it recursively.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources