| Author |
 Topic  |
|
|
Tahsin
Starting Member
USA
34 Posts |
 Posted - 08/07/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/08/2012 : 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
USA
34 Posts |
Posted - 08/08/2012 : 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 - 08/09/2012 : 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
USA
34 Posts |
Posted - 08/09/2012 : 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 - 08/10/2012 : 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. |
|
|
| |
Topic |
|
|
|
Dynamic Pivot/Transposition
