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

skull52Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
I think this should be do it... if not last one.

;WITH CTE AS
(
	SELECT STRNG132 AS Caliber , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk
	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)
)
,CTE1 AS 
(
	SELECT          STRNG132 AS Model, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk
	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)
)
SELECT c.Caliber , c1.Model FROM CTE1 c1 INNER JOIN CTE c ON c.rnk = c1.rnk

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
is possible can you please show few rows from the output you current have and the output you need.
0
 
Pawan KumarDatabase ExpertCommented:
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

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
skull52Author Commented:
Caliber Value Current
STRNG132 
.68 CAL                                                                                                                              
10GA                                                                                                                                 
10MM AUTO                                                                                                                            
12GA                                                                                                                                 
12GA/22LR

Open in new window

 

Model Value Current
STRNG132
#1                                                                                                                                   
03A3                                                                                                                                 
1-A                                                                                                                                  
1-AE                                                                                                                                 
1-B 

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
0
 
Pawan KumarDatabase ExpertCommented:
Please check my last comment...?
0
 
skull52Author Commented:
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'.
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
skull52Author Commented:
Bingo.... that's what i needed, thanks, Pawan
0
 
Pawan KumarDatabase ExpertCommented:
glad to help as always. :)
0
 
skull52Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.