How to pivot 2 or more values in a nested manner

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to pivot 2 or more values in a nested manner

Author

Topic

ibbo14
Starting Member

10 Posts

Posted - 2014-10-21 : 22:27:21

Hi SQL experts,

I am currently working on an export view for a client and they have requested for a pivot with an additional column followed by the first pivot column in order to add an extra detail.

What I have is this:



SAMPLE_TAG	 SAMPLE_TYPE	LAB_METHOD	LAB_ELEMENT	LAB_RESULT_NUMERIC
MCR0004140  	 OR	        ME-MS41	   	 Ag	       	 0.03
MCR0004140  	 OR	        ME-MS41	   	 Au      	-0.2
MCR0004140 	 OR	        ME-MS41	   	 Cu	       	15.3
MCR0004140 	 OR	        ME-MS41	  	 Zn	       	19
MCR0005015 	 OR	        AG-OG46    	 Ag	       	421
MCR0005015  	 OR	        ME-MS41	   	 Au	       	-0.2
MCR0005015  	 OR	        ME-MS41   	 Cu	       	30.7
MCR0005015 	 OR	        ME-MS41   	 Zn	       	15



I want to achieve this:

SAMPLE_TAG	SAMPLE_TYPE 	Ag	    Ag_Method	Au	    Au_Method	Cu	    Cu_Method	Zn	Zn_Method
MCR0004140	OR       	0.03	    ME-MS41	-0.2	    ME-MS41	15.3	    ME-MS41	19	ME-MS41
MCR0005015	OR	        421	    AG-OG46 	-0.2	    ME-MS41	30.7	    ME-MS41	15	ME-MS41

I can get the first pivot to work using the following:

SELECT *
FROM TEMP_PIVOT

PIVOT (
MAX(LAB_RESULT_NUMERIC)
FOR LAB_ELEMENT IN ( [Au],[Ag],[Cu],[Zn],[Pt]) ) AS RESULT

However, this is where I get stuck.

Any help or suggestions would be greatly appreciated. I have attached the create table / insert scripts below:

Thanks :)

-----------------------

CREATE TABLE [dbo].[TEMP_PIVOT](
[LAB_ID] [nvarchar](12) NOT NULL,
[DESPATCH_ID] [nvarchar](30) NULL,
[LAB_JOB_NO] [nvarchar](20) NULL,
[PROJECT] [nvarchar](16) NULL,
[SITE_ID] [nvarchar](16) NULL,
[SAMPLE_TAG] [nvarchar](16) NULL,
[SAMPLE_TYPE] [nvarchar](8) NULL,
[LAB_METHOD] [nvarchar](30) NOT NULL,
[LAB_ELEMENT] [nvarchar](8) NULL,
[LAB_RESULT_NUMERIC] [float] NULL) ON [PRIMARY]

INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0004140', N'OR', N'ME-MS41', N'Ag', 0.03)

INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0004140', N'OR', N'ME-MS41', N'Au', -0.2)

INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0004140', N'OR', N'ME-MS41', N'Cu', 15.3)

INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0004140', N'OR', N'ME-MS41', N'Zn', 19)

INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0005015', N'OR', N'AG-OG46', N'Ag', 421)

INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0005015', N'OR', N'ME-MS41', N'Au', -0.2)

INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0005015', N'OR', N'ME-MS41', N'Cu', 30.7)

INSERT [dbo].[TEMP_PIVOT] ([LAB_ID], [DESPATCH_ID], [LAB_JOB_NO], [PROJECT], [SITE_ID], [SAMPLE_TAG], [SAMPLE_TYPE], [LAB_METHOD], [LAB_ELEMENT], [LAB_RESULT_NUMERIC]) VALUES (N'ALS_MDZ', N'MIR00717', N'AN14036339', N'GORBEA', N'POINT_SAMPLE', N'MCR0005015', N'OR', N'ME-MS41', N'Zn', 15)

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2014-10-21 : 23:15:09

Try something like this:


-- Using PIVOT
SELECT T.SAMPLE_TAG, T.SAMPLE_TYPE, T.Ag, T2.Ag AS Ag_Method, T.Au, T2.Au AS Au_Method, T.Cu, T2.Cu AS Cu_Method, T.Zn, T2.Zn AS Zn_Method
  FROM (SELECT SAMPLE_TAG, SAMPLE_TYPE, Ag, Au, Cu, Zn
          FROM (SELECT SAMPLE_TAG, SAMPLE_TYPE, LAB_RESULT_NUMERIC, LAB_ELEMENT
                  FROM TEMP_PIVOT) AS T
                       PIVOT(MAX(LAB_RESULT_NUMERIC)
                         FOR LAB_ELEMENT IN([Au],[Ag],[Cu],[Zn])) AS RESULT) AS T
	   INNER JOIN
       (SELECT SAMPLE_TAG, SAMPLE_TYPE, Ag, Au, Cu, Zn
          FROM (SELECT SAMPLE_TAG, SAMPLE_TYPE, LAB_METHOD, LAB_ELEMENT
                  FROM TEMP_PIVOT) AS T
                       PIVOT(MAX(LAB_METHOD)
                         FOR LAB_ELEMENT IN([Au],[Ag],[Cu],[Zn])) AS RESULT) AS T2
	   ON T2.SAMPLE_TAG = T.SAMPLE_TAG
	      AND T2.SAMPLE_TYPE = T.SAMPLE_TYPE;

-- Another way using GROUP BY and should be faster
SELECT T.SAMPLE_TAG, T.SAMPLE_TYPE,
       MAX(CASE WHEN T.LAB_ELEMENT = N'Ag' THEN T.LAB_RESULT_NUMERIC ELSE NULL END) AS Ag,
       MAX(CASE WHEN T.LAB_ELEMENT = N'Ag' THEN T.LAB_METHOD ELSE NULL END) AS Ag_Method,
       MAX(CASE WHEN T.LAB_ELEMENT = N'Au' THEN T.LAB_RESULT_NUMERIC ELSE NULL END) AS Au,
       MAX(CASE WHEN T.LAB_ELEMENT = N'Au' THEN T.LAB_METHOD ELSE NULL END) AS Au_Method,
       MAX(CASE WHEN T.LAB_ELEMENT = N'Cu' THEN T.LAB_RESULT_NUMERIC ELSE NULL END) AS Cu,
       MAX(CASE WHEN T.LAB_ELEMENT = N'Cu' THEN T.LAB_METHOD ELSE NULL END) AS Cu_Method,
       MAX(CASE WHEN T.LAB_ELEMENT = N'Zn' THEN T.LAB_RESULT_NUMERIC ELSE NULL END) AS Zn,
       MAX(CASE WHEN T.LAB_ELEMENT = N'Zn' THEN T.LAB_METHOD ELSE NULL END) AS Zn_Method
  FROM TEMP_PIVOT AS T
 GROUP BY T.SAMPLE_TAG, T.SAMPLE_TYPE;