RE: Pivot over multiple columns using the 1.1, 1.2, 2.1, 2.2 sequence

SSC Veteran

Points: 282

July 8, 2014 at 11:04 am

Luis, thank for the reply and reference to proper protocol which I will save for future reference. Here is the information you requested:

====================================================================================

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable

(

Dex_Row_Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,

SampleNo INT NOT NULL,

PAProjid nchar(10) NOT NULL,

TestType1a nvarchar(30),

Complete_Date date

)

--===== Setup any special required conditions especially where dates are concerned

SET DATEFORMAT YMD

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table

INSERT INTO #mytable

(SampleNo, PAprojid, TestType1a, Complete_Date, Dex_Row_Id)

SELECT '2897','L7537 ','Biochemical Oxygen Demand','2014-04-11','2137' UNION ALL

SELECT '2897','L7537 ','Total Suspended Solids','2014-04-14','2138' UNION ALL

SELECT '2953','L7518 ','Total Coliform','2014-04-16','2532' UNION ALL

SELECT '3307','L7516 ','Total Suspended Solids','2014-05-12','4785' UNION ALL

SELECT '3308','L7516 ','Grease & Oil','2014-05-12','4786' UNION ALL

SELECT '3335','L9187 ','Biochemical Oxygen Demand','2014-05-07','4881' UNION ALL

SELECT '3335','L9187 ','Total Suspended Solids','2014-05-08','4882' UNION ALL

SELECT '3350','L7533 ','Biochemical Oxygen Demand','2014-05-07','4948' UNION ALL

SELECT '3350','L7533 ','Total Suspended Solids','2014-05-08','4949' UNION ALL

SELECT '3374','L1763 ','Total Suspended Solids','2014-05-08','5043' UNION ALL

SELECT '3399','L7537 ','Biochemical Oxygen Demand','2014-05-08','5139' UNION ALL

SELECT '3399','L7537 ','Total Suspended Solids','2014-05-08','5140' UNION ALL

SELECT '3411','L1305 ','Biochemical Oxygen Demand','2014-05-08','5187' UNION ALL

SELECT '3411','L1305 ','Total Suspended Solids','2014-05-12','5188' UNION ALL

SELECT '3457','L7507 ','Biochemical Oxygen Demand','2014-05-09','5389' UNION ALL

SELECT '3457','L7507 ','Total Suspended Solids','2014-05-12','5390' UNION ALL

SELECT '3510','L0000 ','Cyanide','2014-05-28','5722' UNION ALL

SELECT '3633','L7533 ','Biochemical Oxygen Demand','2014-05-21','9020' UNION ALL

SELECT '3633','L7533 ','Total Suspended Solids','2014-05-28','9021' UNION ALL

SELECT '3708','L2485 ','Grease & Oil','2014-05-29','9274' UNION ALL

SELECT '3853','L9567 ','Biochemical Oxygen Demand','2014-06-04','9642' UNION ALL

SELECT '3853','L9567 ','Total Suspended Solids','2014-06-05','9643' UNION ALL

SELECT '4088','L1763 ','Total Suspended Solids','2014-06-12','10450' UNION ALL

SELECT '4176','L7671 ','Grease & Oil','2014-06-13','10687' UNION ALL

SELECT '4217','L7545 ','Biochemical Oxygen Demand','2014-06-18','10825' UNION ALL

SELECT '4238','L7570 ','Ammonia','2014-06-23','10858' UNION ALL

SELECT '4238','L7570 ','Total Solids','2014-06-23','10860' UNION ALL

SELECT '4341','L8419 ','Total Solids','2014-06-30','11121' UNION ALL

SELECT '4342','L8419 ','Total Solids','2014-06-30','11126'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT #mytable ON

SELECT * FROM #mytable

============================================================================================

There appears to be a problem with a NULL Complete_Date. "NULL" is representing an entire row with NULL as the Complete_Date value.

The above is cleaned up from the following:

SELECT 'SELECT '

+ QUOTENAME(SampleNo,'''')+','

+ QUOTENAME(PAProjId,'''')+','

+ QUOTENAME(TestType1a,'''')+','

+ QUOTENAME(Complete_Date,'''')+','

+ QUOTENAME(Dex_Row_Id,'''')

+ ' UNION ALL'

FROM Analytical_Sample_Log_ResultsInfo

WHERE SampleNo < 4372