Pivot and Split a table

Question

Given the following table structure

ID	Description	Type	FieldCount	Field1	Field2	Field3	Field4	Field5	Field6
1	Apple	Fruit	4	Expires	3/1/2013	MinQty	100
2	Car	Vehicle	6	Wheels	4	Weight	2500 Kg	Passengers	5
3	Banana	Fruit	4	Expires	2/28/2013	MinQty	50
4	Motorcycle	Vehicle	6	Wheels	2	Weight	1000 Kg	Passengers	2

what is the best way to parse them into two separate tables (Fruits and Vehicles) like below?

ID	Description	Expires	MinQty
1	Apple	3/1/2013	100
3	Banana	2/28/2013	50

ID	Description	Wheels	Weight	Passengers
1	Car	4	2500 Kg	5
3	Motorcycle	2	1000 Kg	2

note that the field count is fixed depending on the description. There are roughly 180 fields in the original table or a 90 field-value combination so a simple case select would be challenging.  I know up front all the possible fields for a specific type as well.

I was able to cobble together some code roughly achieving inserting into the vehicle table. My usage of pivots are kind of nasty though so now wondering if there's a more elegant solution.

CREATE TABLE #Original (ID INT IDENTITY(1,1), Description VARCHAR(100), Type varchar(50), FieldCount int, field1 varchar(50), field2 varchar(50), field3 varchar(50), field4 varchar(50), field5 varchar(50), field6 varchar(50))

INSERT INTO #Original (Description, Type, FieldCount, field1, field2, field3, field4, field5, field6)
SELECT 'Apple', 'Fruit', 4, 'Expires', '3/1/2013', 'MinQty', '100', '', ''
UNION
SELECT 'Car', 'Vehicle', 6, 'Wheels', '4', 'Weight', '2500 Kg', 'Passengers', '5'
UNION
SELECT 'Banana', 'Fruit', 4, 'Expires', '2/28/2013', 'MinQty', '50', '', ''
UNION
SELECT 'Motorcycle', 'Vehicle', 6, 'Wheels', '2', 'Weight', '1000 Kg', 'Passengers', '2'
UNION
SELECT 'Boat', 'Vehicle', 4, 'Length', '100 ft', 'Top Speed', '5 Knots','',''
UNION
SELECT 'Rocket', 'Vehicle', 4, 'Top Speed', 'Mach 10', 'Altitude', '10000 Mi','',''

SELECT Description, TYPE, 
MAX(Wheels) AS Wheels, MAX(Length) AS Length, MAX([Top Speed]) AS TopSpeed, 
MAX(Altitude) AS Altitude, MAX([Passengers]) AS Passengers
FROM
(
SELECT Description, TYPE, FieldCount,[Wheels],[Length],[Top Speed],[Altitude],[Passengers]
FROM ( SELECT * FROM #Original WHERE Type = 'Vehicle') src1
PIVOT	(MAX(Field2) FOR Field1 IN ([Wheels],[Length],[Top Speed],[Altitude],[Passengers])) as pvt1
UNION SELECT Description, TYPE, FieldCount,[Wheels],[Length],[Top Speed],[Altitude],[Passengers]
FROM ( SELECT * FROM #Original WHERE Type = 'Vehicle') src1
PIVOT	(MAX(Field4) FOR Field3 IN ([Wheels],[Length],[Top Speed],[Altitude],[Passengers])) as pvt1
UNION SELECT Description, TYPE, FieldCount,[Wheels],[Length],[Top Speed],[Altitude],[Passengers]
FROM ( SELECT * FROM #Original WHERE Type = 'Vehicle') src1
PIVOT	(MAX(Field6) FOR Field5 IN ([Wheels],[Length],[Top Speed],[Altitude],[Passengers])) as pvt1
) final
GROUP BY Description, TYPE

Thanks in advance

---

JPL

Answer 1

Do you know values of FieldCountField1 and Field3 in advance?

E.g.

select Id, Description, max(case when FieldCountField1 = 'Expires' then Field2 end) as Expires,

max(case when Field3 = 'MinQty' then Field4 end) from MyEAVTable

WHERE [Type] = 'Fruit'

GROUP BY Id, Description

and similar code for Vehicles. 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

CREATE TABLE FRUIT_tbl (ID INT,Description VARCHAR(50),Expires VARCHAR(50),MinQty INT)
INSERT INTO FRUIT_tbl SELECT ID,Description,Field2,Field4 FROM Original_tbl WHERE Type = 'Fruit' AND Field1 = 'Expires' AND
Field3 = 'MinQty'
CREATE TABLE Vehicle_tbl (ID INT,Description VARCHAR(50),Wheels INT,Weight VARCHAR(50),Passengers INT)
INSERT INTO Vehicle_tbl SELECT ID,Description,Field2,Field4,Field6 FROM Original_tbl WHERE Type = 'Vehicle' AND Field1 = 'Wheels' AND
Field3 = 'Weight' AND Field5 = 'Passengers'

---

Thanks & Regards, sathya

Answer 3

sorry - looks like pasting the table into the forum did not come out as expected. I can tell the values in FieldCount up front. 

The odd numbered Fields are always the field names and the even numbered contains the values.

---

JPL

Answer 4

Hi there,

I did not see any challenge here un less I do not understand the question.

From my understanding your first table can easity split in to two tables.

Below query make your required first table

Select ID,
Description,
Field2 as [Expires],
Field4 as [MinQty]
From Table as t1
Where t1. FieldCount = 4 and t1. Type = 'Fruit'

Below query make your required second table.

Select ID,
Description,
Field2 as [ Wheels],
Field4 as [Weight],
Field6 as [Passengers]
From Table as t1
Where t1. FieldCount = 6 and t1. Type = 'Vehicle'

Answer 5

that's a good try Kumar but it would not work in this case. Please check out the sample SQL to illustrate the problem where the fields following the Field Count is jagged.

---

JPL

Answer 6

One way.  It requires a number table (see http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html for what a number table is, how to create one, and some of their many uses.  For purposes of this response, you can easily create a temporary one with

Create Table #Numbers(Number int primary key);
Insert #Numbers(Number) Values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

Once you have it (you need at least as many numbers in the Number table as the maximum value of FieldCount / 2), you can extract the vehicle information with

;With cte As
(Select Description, TYPE, 
   Case n.Number When 1 Then o.field1 When 2 Then o.field3 When 3 Then field5 End As Attribute,
   Case n.Number When 1 Then o.field2 When 2 Then o.field4 When 3 Then field6 End As AttributeValue
From #Original o
Inner Join #Numbers n On n.Number <= o.FieldCount / 2
Where o.Type = 'Vehicle')
Select Description, Type, 
   Max(Case When Attribute = 'Wheels' Then AttributeValue End) As Wheels,
   Max(Case When Attribute = 'Length' Then AttributeValue End) As Length,
   Max(Case When Attribute = 'Top Speed' Then AttributeValue End) As TopSpeed,
   Max(Case When Attribute = 'Altitude' Then AttributeValue End) As Altitude,
   Max(Case When Attribute = 'Passengers' Then AttributeValue End) As Passengers
From cte
Group By Description, Type;

This has an advantage over using the multiple PIVOT's because it only scans the table one time.  The code you posted with the UNION of three pivots will scan the table 3 times.

In addition, the code is (at least to me) simpler to read and maintain.  For example, if you add a new attribute Height to the information you have about vehicles, the only change needed is to add a new column in the final response

   Max(Case When Attribute = 'Height' Then AttributeValue End) As Height

Tom

Answer 7

This is awesome. Thanks Tom

---

JPL