UNPIVOT

[747576]

Hi,
I would like some help with some sql I am trying to develop to UNPIVOT a flat table, and normalise the data.

AT_TEMP is the flat table, I am using the UNPIVOT fuctionality in sql but with unions to get the results, I'd like to know how to make it more dynamic so I don't have to use unions and copy the sql, as the source table can be quite large.

Thank you

DROP TABLE AT_TEMP
GO

CREATE TABLE AT_TEMP([ID] VARCHAR(10), [NAME1] FLOAT, [FORM1] VARCHAR(10), [NAME2] FLOAT, [FORM2] VARCHAR(10),[NAME3] FLOAT, [FORM3] VARCHAR(10))
GO

INSERT INTO AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A1','7','ABC','2','ADFR','55','FRED')
INSERT INTO AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A2','5','ABC','2','MA','33','FDE')
INSERT INTO AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A3','3','ABC','2','ABC','5','OE')
INSERT INTO AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A4','6','MA','4','MA','4','OE')
INSERT INTO AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A5','1','ABC','2','MA','3','MA')
INSERT INTO AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A6','7','ABC','2','MA','1','OE')


--Normalise data
SELECT ID, NAME, VALUE, FORM1 FROM (

SELECT DISTINCT	 ID
		,NAME1
		,FORM1
FROM AT_TEMP
)p
unpivot
(VALUE FOR [NAME] IN (
	NAME1
					) 
) A
WHERE VALUE <> ''

UNION

SELECT ID, NAME, VALUE, FORM2 FROM (

SELECT DISTINCT	 ID
		,NAME2
		,FORM2
FROM AT_TEMP
)p
unpivot
(VALUE FOR [NAME] IN (
	NAME2
					) 
) A
WHERE VALUE <> ''

UNION

SELECT ID, NAME, VALUE, FORM3 FROM (

SELECT DISTINCT	 ID
		,NAME3
		,FORM3
FROM AT_TEMP
)p
unpivot
(VALUE FOR [NAME] IN (
	NAME3
					) 
) A
WHERE VALUE <> ''

view required:


A1 NAME1 7 ABC
A1 NAME2 2 ADFR
A1 NAME3 55 FRED
A2 NAME1 5 ABC
A2 NAME2 2 MA
A2 NAME3 33 FDE
A3 NAME1 3 ABC
A3 NAME2 2 ABC
A3 NAME3 5 OE
A4 NAME1 6 MA
A4 NAME2 4 MA
A4 NAME3 4 OE
A5 NAME1 1 ABC
A5 NAME2 2 MA
A5 NAME3 3 MA
A6 NAME1 7 ABC
A6 NAME2 2 MA
A6 NAME3 1 OE

 

[EKOnerhime]

CREATE TABLE #AT_TEMP([ID] VARCHAR(10), [NAME1] FLOAT, [FORM1] VARCHAR(10), [NAME2] FLOAT, [FORM2] VARCHAR(10),[NAME3] FLOAT, [FORM3] VARCHAR(10))

INSERT INTO #AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A1','7','ABC','2','ADFR','55','FRED')
INSERT INTO #AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A2','5','ABC','2','MA','33','FDE')
INSERT INTO #AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A3','3','ABC','2','ABC','5','OE')
INSERT INTO #AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A4','6','MA','4','MA','4','OE')
INSERT INTO #AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A5','1','ABC','2','MA','3','MA')
INSERT INTO #AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A6','7','ABC','2','MA','1','OE')


SELECT ID, [Name], NameValues, FormValues
FROM
(SELECT ID, [Name1], [Name2], [Name3], [Form1], [Form2], [Form3]
FROM #AT_TEMP) p
UNPIVOT
(NameValues FOR [Name] IN
([Name1], [Name2], [Name3])
)AS NamePivot
UNPIVOT
(FormValues FOR [Form] IN
([Form1], [Form2], [Form3])
)AS FormPivot

drop table #AT_TEMP

A1 Name1 7 ABC
A1 Name1 7 ADFR
A1 Name1 7 FRED
A1 Name2 2 ABC
A1 Name2 2 ADFR
A1 Name2 2 FRED
A1 Name3 55 ABC
A1 Name3 55 ADFR
A1 Name3 55 FRED
A2 Name1 5 ABC
A2 Name1 5 MA
A2 Name1 5 FDE
A2 Name2 2 ABC
A2 Name2 2 MA
A2 Name2 2 FDE
A2 Name3 33 ABC
A2 Name3 33 MA
A2 Name3 33 FDE
A3 Name1 3 ABC
A3 Name1 3 ABC
A3 Name1 3 OE
A3 Name2 2 ABC
A3 Name2 2 ABC
A3 Name2 2 OE
A3 Name3 5 ABC
A3 Name3 5 ABC
A3 Name3 5 OE
A4 Name1 6 MA
A4 Name1 6 MA
A4 Name1 6 OE
A4 Name2 4 MA
A4 Name2 4 MA
A4 Name2 4 OE
A4 Name3 4 MA
A4 Name3 4 MA
A4 Name3 4 OE
A5 Name1 1 ABC
A5 Name1 1 MA
A5 Name1 1 MA
A5 Name2 2 ABC
A5 Name2 2 MA
A5 Name2 2 MA
A5 Name3 3 ABC
A5 Name3 3 MA
A5 Name3 3 MA
A6 Name1 7 ABC
A6 Name1 7 MA
A6 Name1 7 OE
A6 Name2 2 ABC
A6 Name2 2 MA
A6 Name2 2 OE
A6 Name3 1 ABC
A6 Name3 1 MA
A6 Name3 1 OE

Is this what you were looking for?

 

[EKOnerhime]

Ignore the above, second attempt:

CREATE TABLE #AT_TEMP([ID] VARCHAR(10), [NAME1] FLOAT, [FORM1] VARCHAR(10), [NAME2] FLOAT, [FORM2] VARCHAR(10),[NAME3] FLOAT, [FORM3] VARCHAR(10))

INSERT INTO #AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A1','7','ABC','2','ADFR','55','FRED')
INSERT INTO #AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A2','5','ABC','2','MA','33','FDE')
INSERT INTO #AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A3','3','ABC','2','ABC','5','OE')
INSERT INTO #AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A4','6','MA','4','MA','4','OE')
INSERT INTO #AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A5','1','ABC','2','MA','3','MA')
INSERT INTO #AT_TEMP([ID],[NAME1],[FORM1],[NAME2],[FORM2],[NAME3],[FORM3]) VALUES('A6','7','ABC','2','MA','1','OE')


SELECT ID, [Name], NameValues, FormValues
FROM
(SELECT ID, [Name1], [Name2], [Name3], [Form1], [Form2], [Form3]
FROM #AT_TEMP) p
UNPIVOT
(NameValues FOR [Name] IN
([Name1], [Name2], [Name3])
)AS NamePivot
UNPIVOT
(FormValues FOR [Form] IN
([Form1], [Form2], [Form3])
)AS FormPivot
WHERE REPLACE([Name],'Name','') = REPLACE([Form],'Form','')

drop table #AT_TEMP

Result:
A1 Name1 7 ABC
A1 Name2 2 ADFR
A1 Name3 55 FRED
A2 Name1 5 ABC
A2 Name2 2 MA
A2 Name3 33 FDE
A3 Name1 3 ABC
A3 Name2 2 ABC
A3 Name3 5 OE
A4 Name1 6 MA
A4 Name2 4 MA
A4 Name3 4 OE
A5 Name1 1 ABC
A5 Name2 2 MA
A5 Name3 3 MA
A6 Name1 7 ABC
A6 Name2 2 MA
A6 Name3 1 OE