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;




For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha
Go to Top of Page

ibbo14
Starting Member

10 Posts

Posted - 2014-10-21 : 23:21:46
Thanks for the reply malpashaa, I'll give that a go and let you know shortly.
Go to Top of Page

ibbo14
Starting Member

10 Posts

Posted - 2014-10-22 : 00:44:23
Hi Muhammad,

I got it going using the group by approach, you are right about it being much faster.

Thank you very much for your help :)
Go to Top of Page

ElenaSTL
Starting Member

10 Posts

Posted - 2014-10-23 : 14:43:33
Malpashaa,
what is the purpose to put N before the element's name? like:
WHEN T.LAB_ELEMENT = N'Ag'
It works the same way without N.
Go to Top of Page
   

- Advertisement -