asked on

Pivot a single column with different values into multiple column

I have a table the has a column that has multiple values and has an ID for each value, for example "Extender_List_ID = 2" is for Caliber and "Extender_List_ID = 1" is for the Model. both values are in the same column "STRNG132" I want to separate them into 2 columns (MODEL CALIBER) based on the ID's

SELECT     STRNG132 AS Caliber
FROM            (SELECT        a.Extender_Window_ID, b.FIELDNAM, b.Extender_List_ID, c.Extender_List_Item_ID, c.STRNG132
                          FROM            EXT20021 AS c INNER JOIN
                                                    EXT20010 AS b ON c.Extender_List_ID = b.Extender_List_ID CROSS JOIN
                                                    EXT20100 AS a
                          WHERE        (a.Extender_Window_ID = 'Firearminfo') AND (a.Extender_Field_IDs_1 = b.Field_ID) OR
                                                    (a.Extender_Window_ID = 'Firearminfo') AND (a.Extender_Field_IDs_2 = b.Field_ID) OR
                                                    (a.Extender_Window_ID = 'Firearminfo') AND (a.Extender_Field_IDs_3 = b.Field_ID)) AS FI
WHERE        (Extender_List_ID = 2)

SELECT          STRNG132 AS Model
FROM            (SELECT        a.Extender_Window_ID, b.FIELDNAM, b.Extender_List_ID, c.Extender_List_Item_ID, c.STRNG132
                          FROM            EXT20021 AS c INNER JOIN
                                                    EXT20010 AS b ON c.Extender_List_ID = b.Extender_List_ID CROSS JOIN
                                                    EXT20100 AS a
                          WHERE        (a.Extender_Window_ID = 'Firearminfo') AND (a.Extender_Field_IDs_1 = b.Field_ID) OR
                                                    (a.Extender_Window_ID = 'Firearminfo') AND (a.Extender_Field_IDs_2 = b.Field_ID) OR
                                                    (a.Extender_Window_ID = 'Firearminfo') AND (a.Extender_Field_IDs_3 = b.Field_ID)) AS FI
WHERE        (Extender_List_ID = 1)

Open in new window

Pawan Kumar

is possible can you please show few rows from the output you current have and the output you need.

Pawan Kumar

Please check if this helps.

SELECT *
FROM            
(
	SELECT        
		a.Extender_Window_ID, b.FIELDNAM, b.Extender_List_ID, c.Extender_List_Item_ID
		,CASE WHEN Extender_List_ID = 1 THEN c.STRNG132 END Model
		,CASE WHEN Extender_List_ID = 2 THEN c.STRNG132 END Caliber	 												
		FROM            EXT20021 AS c INNER JOIN
		EXT20010 AS b ON c.Extender_List_ID = b.Extender_List_ID CROSS JOIN
		EXT20100 AS a
		WHERE        (a.Extender_Window_ID = 'Firearminfo') AND (a.Extender_Field_IDs_1 = b.Field_ID) OR
		(a.Extender_Window_ID = 'Firearminfo') AND (a.Extender_Field_IDs_2 = b.Field_ID) OR
		(a.Extender_Window_ID = 'Firearminfo') AND (a.Extender_Field_IDs_3 = b.Field_ID)
												
) AS FI

Open in new window

Fred Webb

ASKER

Caliber Value Current

Open in new window

Model Value Current

Open in new window

This is what I want it to look like
Model Caliber
#1 .68 CAL
03A3 10GA
1-A 10MM AUTO
1-AE 12GA
1-B 12GA/22LR

Pawan Kumar

Please check my last comment...?

Fred Webb

ASKER

got the following error

Msg 209, Level 16, State 1, Line 40
Ambiguous column name 'Extender_List_ID'.
Msg 209, Level 16, State 1, Line 41
Ambiguous column name 'Extender_List_ID'.

Pawan Kumar

sorry. my bad. updated.

SELECT *
FROM            
(
	SELECT        
		a.Extender_Window_ID, b.FIELDNAM, b.Extender_List_ID, c.Extender_List_Item_ID
		,CASE WHEN c.Extender_List_ID = 1 THEN c.STRNG132 END Model
		,CASE WHEN c.Extender_List_ID = 2 THEN c.STRNG132 END Caliber	 												
		FROM            EXT20021 AS c INNER JOIN
		EXT20010 AS b ON c.Extender_List_ID = b.Extender_List_ID CROSS JOIN
		EXT20100 AS a
		WHERE        (a.Extender_Window_ID = 'Firearminfo') AND (a.Extender_Field_IDs_1 = b.Field_ID) OR
		(a.Extender_Window_ID = 'Firearminfo') AND (a.Extender_Field_IDs_2 = b.Field_ID) OR
		(a.Extender_Window_ID = 'Firearminfo') AND (a.Extender_Field_IDs_3 = b.Field_ID)
												
) AS FI

Open in new window

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fred Webb

ASKER

Bingo.... that's what i needed, thanks, Pawan

Pawan Kumar

glad to help as always. :)

Fred Webb

ASKER

Thanks