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

Question

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

LHendren

SSC Veteran

Points: 282
More actions
July 7, 2014 at 2:03 pm

#288359

I am using the following stored procedure which is then used in a tableadapter. My problem is that although 90%+ of the data generated is correct, I have an almost 10% occurrence of complete_dates pairing with the wrong test_Type; therefore, there must be something fundamentally wrong with my pivots. The dbo.Analytical_Sample_Log_ResultsInfo table has been double checked for accuracy, so no issue there.
-- pivot over multiple columns using the 1.1, 1.2, 2.1, 2.2 sequence
SELECT SampleNo,
max([1.1]) AS Test_Type1,
max([1.2]) AS Complete_Date1,
max([1.3]) AS DateAndTime,
max([1.4]) AS Comments1a,
max([1.5]) AS PAprojid,
max([2.1]) AS Test_Type2,
max([2.2]) AS Complete_Date2,
max([3.1]) AS Test_Type3,
max([3.2]) AS Complete_Date3,
max([4.1]) AS Test_Type4,
max([4.2]) AS Complete_Date4,
max([5.1]) AS Test_Type5,
max([5.2]) AS Complete_Date5,
max([6.1]) AS Test_Type6,
max([6.2]) AS Complete_Date6,
max([7.1]) AS Test_Type7,
max([7.2]) AS Complete_Date7,
max([8.1]) AS Test_Type8,
max([8.2]) AS Complete_Date8,
max([9.1]) AS Test_Type9,
max([9.2]) AS Complete_Date9,
max([10.1]) AS Test_Type10,
max([10.2]) AS Complete_Date10,
max([11.1]) AS Test_Type11,
max([11.2]) AS Complete_Date11,
max([12.1]) AS Test_Type12,
max([12.2]) AS Complete_Date12,
max([13.1]) AS Test_Type13,
max([13.2]) AS Complete_Date13,
max([14.1]) AS Test_Type14,
max([14.2]) AS Complete_Date14,
max([15.1]) AS Test_Type15,
max([15.2]) AS Complete_Date15,
max([16.1]) AS Test_Type16,
max([16.2]) AS Complete_Date16,
max([17.1]) AS Test_Type17,
max([17.2]) AS Complete_Date17,
max([18.1]) AS Test_Type18,
max([18.2]) AS Complete_Date18,
max([19.1]) AS Test_Type19,
max([19.2]) AS Complete_Date19,
max([20.1]) AS Test_Type20,
max([20.2]) AS Complete_Date20,
max([21.1]) AS Test_Type21,
max([21.2]) AS Complete_Date21,
max([22.1]) AS Test_Type22,
max([22.2]) AS Complete_Date22,
max([23.1]) AS Test_Type23,
max([23.2]) AS Complete_Date23,
max([24.1]) AS Test_Type24,
max([24.2]) AS Complete_Date24,
max([25.1]) AS Test_Type25,
max([25.2]) AS Complete_Date25,
max([26.1]) AS Test_Type26,
max([26.2]) AS Complete_Date26
FROM
(SELECT s.SampleNo, s.PAprojid, s.DateAndTime, s.TestType1a, s.Complete_Date, s.Comments1a,
cast(row_number() OVER (PARTITION BY SampleNo ORDER BY TestType1a) AS VARCHAR(2)) + '.1' AS TestType1aSequence,
cast(row_number() OVER (PARTITION BY SampleNo ORDER BY Complete_Date) AS VARCHAR(2)) + '.2' AS Complete_DateSequence,
cast(row_number() OVER (PARTITION BY SampleNo ORDER BY DateAndTime) AS VARCHAR(2)) + '.3' AS DateAndTimeSequence,
cast(row_number() OVER (PARTITION BY SampleNo ORDER BY Comments1a) AS VARCHAR(2)) + '.4' AS Comments1aSequence,
cast(row_number() OVER (PARTITION BY SampleNo ORDER BY PAprojid) AS VARCHAR(2)) + '.5' AS PAprojidSequence
FROM dbo.Analytical_Sample_Log_ResultsInfo AS S) AS P
PIVOT (max(TestType1a) FOR TestType1aSequence IN ([1.1], [2.1], [3.1], [4.1], [5.1], [6.1], [7.1],[8.1], [9.1], [10.1], [11.1], [12.1], [13.1], [14.1], [15.1], [16.1], [17.1], [18.1], [19.1], [20.1], [21.1], [22.1], [23.1], [24.1], [25.1], [26.1])) AS pivot1
PIVOT (max(Complete_Date) FOR Complete_DateSequence IN ([1.2], [2.2], [3.2], [4.2], [5.2], [6.2], [7.2], [8.2], [9.2], [10.2], [11.2], [12.2], [13.2], [14.2], [15.2], [16.2], [17.2], [18.2], [19.2], [20.2], [21.2], [22.2], [23.2], [24.2], [25.2], [26.2])) AS pivot2
PIVOT (max(DateAndTime) FOR DateAndTimeSequence IN ([1.3])) AS pivot3
PIVOT (max(Comments1a) FOR Comments1aSequence IN ([1.4])) AS pivot4
PIVOT (max(PAprojid) FOR PAprojidSequence IN ([1.5])) AS pivot5
GROUP BY SampleNo
END
GO
[font="Arial"][/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Could you post DDL, sample data (as insert into statements) and expected results based on that data? You can read the article linked on my signature to know how to do it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

LHendren SSC Veteran Points: 282 More actions · Answer 2

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