August 23, 2012 at 8:25 am
[font="Courier New"]Hi All
I think I need to set up a crosstab query of some sort but I am totally stuck on how to do it!!!
I do NOT need an aggregate function like SUM, AVG, MAX, etc…
I have a very flat file like this:
HOLE_ID DEPTH DATE ANALYTE RESULT UNITS LOD ACCRED
------- ----- ---- ------- ------ ----- --- ------
BH1 0.5 01/01/2012 As 12.1 mg/kg 0.1 MCERTS
BH1 0.5 01/01/2012 Be 8.2 mg/kg 0.1 MCERTS
BH1 0.5 01/01/2012 Cd 0.1 mg/kg 0.1 MCERTS
BH1 0.5 01/01/2012 Cr 555.8 mg/kg 0.1 MCERTS
BH1 0.5 01/01/2012 Cu 2.5 mg/kg 0.1 MCERTS
BH1 1.1 01/02/2012 As 88.6 mg/kg 0.1 MCERTS
BH1 1.1 01/02/2012 Be 14.9 mg/kg 0.1 MCERTS
BH1 1.1 01/02/2012 Cd 4.7 mg/kg 0.1 MCERTS
BH1 1.1 01/02/2012 Cr 492.3 mg/kg 0.1 MCERTS
BH1 1.1 01/02/2012 Cu 99.9 mg/kg 0.1 MCERTS
…… etc
I need a crosstab/report query that gives me something like this…
ANALYTE LOD UNITS ACCRED [BH1 (0.5) 01/01/2012] [BH1 (1.1) 01/02/2012] …
------- --- ----- ------ --- ---
As 0.1 mg/kg MCERTS 12.1 88.6
Be 0.1 mg/kg MCERTS 8.2 14.9
Cd 0.1 mg/kg MCERTS 0.1 4.7
Cr 0.1 mg/kg MCERTS 555.8 492.3
Cu 0.1 mg/kg MCERTS 2.5 99.9
… etc….
Any ideas gratefully received!
Cheers
James
[/font]
August 23, 2012 at 8:35 am
Consider two excellent articles by Jeff Moden:
1. Suggestions about how to post data/code to get the best help:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
2. Pivots and crosstab article (1 of 2 in the series)
August 23, 2012 at 8:53 am
Thanks kl25.
On your first point "oops". Understood. I will check out the second point/link and see if i can get my head around it!
Cheers
James
August 23, 2012 at 9:00 am
--== CREATE SAMPLE DATA (your should've included this in your post) ==--
-- Note, because you have written your "date" in the format you have, I've assumed
-- it is a VARCHAR / CHAR column and not a DATE or DATETIME. This solution will need
-- modifying if that is not the case.
SELECT HOLE_ID, DEPTH, DATE, ANALYTE, RESULT, UNITS, LOD, ACCRED
INTO #yourSampleData
FROM (VALUES('BH1',0.5,'01/01/2012','As',12.1,'mg/kg',0.1,'MCERTS'),
('BH1',0.5,'01/01/2012','Be',8.2,'mg/kg',0.1,'MCERTS'),
('BH1',0.5,'01/01/2012','Cd',0.1,'mg/kg',0.1,'MCERTS'),
('BH1',0.5,'01/01/2012','Cr',555.8,'mg/kg',0.1,'MCERTS'),
('BH1',0.5,'01/01/2012','Cu',2.5,'mg/kg',0.1,'MCERTS'),
('BH1',1.1,'01/02/2012','As',88.6,'mg/kg',0.1,'MCERTS'),
('BH1',1.1,'01/02/2012','Be',14.9,'mg/kg',0.1,'MCERTS'),
('BH1',1.1,'01/02/2012','Cd',4.7,'mg/kg',0.1,'MCERTS'),
('BH1',1.1,'01/02/2012','Cr',492.3,'mg/kg',0.1,'MCERTS'),
('BH1',1.1,'01/02/2012','Cu',99.9,'mg/kg',0.1,'MCERTS')
)a(HOLE_ID, DEPTH, DATE, ANALYTE, RESULT, UNITS, LOD, ACCRED);
--== STATIC VERSION OF THE QUERY WE WANT ==--
SELECT ANALYTE, LOD, UNITS, ACCRED,
MAX(CASE WHEN HOLE_ID = 'BH1' AND DATE = '01/01/2012' AND DEPTH = 0.5 THEN RESULT END) AS [BH1 (0.5) 01/01/2012],
MAX(CASE WHEN HOLE_ID = 'BH1' AND DATE = '01/02/2012' AND DEPTH = 1.1 THEN RESULT END) AS [BH1 (1.1) 01/02/2012]
FROM #yourSampleData
GROUP BY ANALYTE, LOD, UNITS, ACCRED;
Results in: -
ANALYTE LOD UNITS ACCRED BH1 (0.5) 01/01/2012 BH1 (1.1) 01/02/2012
------- --------------------------------------- ----- ------ --------------------------------------- ---------------------------------------
As 0.1 mg/kg MCERTS 12.1 88.6
Be 0.1 mg/kg MCERTS 8.2 14.9
Cd 0.1 mg/kg MCERTS 0.1 4.7
Cr 0.1 mg/kg MCERTS 555.8 492.3
Cu 0.1 mg/kg MCERTS 2.5 99.9
OK, now we need to go dynamic since we don't want to have to modify the query for every combination manually.
Note, because you have written your "date" in the format you have, I've assumed
it is a VARCHAR / CHAR column and not a DATE or DATETIME. This solution will need
modifying if that is not the case.
--== DYNAMIC VERSION OF THE QUERY WE WANT ==--
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = 'SELECT ANALYTE, LOD, UNITS, ACCRED, ' + CHAR(13) + CHAR(10) +
STUFF((SELECT ','+CHAR(13)+CHAR(10)+sqlCommand
FROM (SELECT DISTINCT
'MAX(CASE WHEN HOLE_ID = '+CHAR(39)+HOLE_ID+CHAR(39)+
' AND DATE = '+CHAR(39)+DATE+CHAR(39)+' AND DEPTH = '+CAST(DEPTH AS VARCHAR(10))+
' THEN RESULT END) AS ['+HOLE_ID+' ('+CAST(DEPTH AS VARCHAR(10))+') '+ DATE+']'
FROM #yourSampleData
)a(sqlCommand)
FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)'),1,3,'') +
'FROM #yourSampleData' + CHAR(13) + CHAR(10) + 'GROUP BY ANALYTE, LOD, UNITS, ACCRED;';
EXECUTE sp_executesql @sql;
Results: -
ANALYTE LOD UNITS ACCRED BH1 (0.5) 01/01/2012 BH1 (1.1) 01/02/2012
------- --------------------------------------- ----- ------ --------------------------------------- ---------------------------------------
As 0.1 mg/kg MCERTS 12.1 88.6
Be 0.1 mg/kg MCERTS 8.2 14.9
Cd 0.1 mg/kg MCERTS 0.1 4.7
Cr 0.1 mg/kg MCERTS 555.8 492.3
Cu 0.1 mg/kg MCERTS 2.5 99.9
August 23, 2012 at 9:02 am
Cadavre beat me to it. I was about to post that re: the second article, note the discussion about the use of MAX vs SUM. It's a helpful technique in situations like the one you posed.
August 24, 2012 at 6:47 am
You are both genii. Thank you very much. A couple of questions which I can assume will be easy for you to answer.
1. How would using the "date" as a DateTime field change things? Will I need to use "CAST"?
2. Being a novice is the "EXECUTE sp_executesql" a build in function whereby you execute the query code as a stored procedure?
As a novice (only having re-joined this group yesterday) I have learned a lot. Not least how to ask the actual question.
Thanks again
James
August 24, 2012 at 6:49 am
Oops! Found the answer to the second question myself at http://msdn.microsoft.com/en-us/library/ms188001.aspx
Cheers
James
August 24, 2012 at 7:32 am
jdnelson.web (8/24/2012)
You are both genii. Thank you very much. A couple of questions which I can assume will be easy for you to answer.1. How would using the "date" as a DateTime field change things? Will I need to use "CAST"?
2. Being a novice is the "EXECUTE sp_executesql" a build in function whereby you execute the query code as a stored procedure?
As a novice (only having re-joined this group yesterday) I have learned a lot. Not least how to ask the actual question.
Thanks again
James
1. Yes, you'd need to cast it, because a DATE/DATETIME can't be appended to the NVARCHAR query string
2. Yep.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply