Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pivot over multiple columns using the 1.1, 1.2, 2.1, 2.2 sequence Expand / Collapse
Author
Message
Posted Monday, July 7, 2014 2:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 6:09 AM
Points: 5, Visits: 53
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]
Post #1590097
Posted Monday, July 7, 2014 2:42 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 3,513, Visits: 7,566
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1590113
Posted Tuesday, July 8, 2014 11:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 6:09 AM
Points: 5, Visits: 53
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

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
NULL
SELECT '4238','L7570 ','Ammonia','2014-06-23','10858' UNION ALL
NULL
SELECT '4238','L7570 ','Total Solids','2014-06-23','10860' UNION ALL
SELECT '4341','L8419 ','Total Solids','2014-06-30','11121' UNION ALL
NULL
NULL
NULL
NULL
SELECT '4342','L8419 ','Total Solids','2014-06-30','11126' UNION ALL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
Post #1590439
Posted Tuesday, July 8, 2014 12:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 3,513, Visits: 7,566
This seems to give the same results as your query. The problem is that you need a single row number, instead of having to calculate a different row number for each column.
Using a cross tab, you'll improve performance as the table will be read once.
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER( PARTITION BY SampleNo ORDER BY Complete_Date) rn
FROM #mytable
)
SELECT SampleNo,
MAX( CASE WHEN rn = 1 THEN TestType1a END) Test_Type1,
MAX( CASE WHEN rn = 1 THEN Complete_Date END) Complete_Date1,
PAProjid,
MAX( CASE WHEN rn = 2 THEN TestType1a END) Test_Type2,
MAX( CASE WHEN rn = 2 THEN Complete_Date END) Complete_Date2,
MAX( CASE WHEN rn = 3 THEN TestType1a END) Test_Type3,
MAX( CASE WHEN rn = 3 THEN Complete_Date END) Complete_Date3,
MAX( CASE WHEN rn = 4 THEN TestType1a END) Test_Type4,
MAX( CASE WHEN rn = 4 THEN Complete_Date END) Complete_Date4,
MAX( CASE WHEN rn = 5 THEN TestType1a END) Test_Type5,
MAX( CASE WHEN rn = 5 THEN Complete_Date END) Complete_Date5,
MAX( CASE WHEN rn = 6 THEN TestType1a END) Test_Type6,
MAX( CASE WHEN rn = 6 THEN Complete_Date END) Complete_Date6,
MAX( CASE WHEN rn = 7 THEN TestType1a END) Test_Type7,
MAX( CASE WHEN rn = 7 THEN Complete_Date END) Complete_Date7,
MAX( CASE WHEN rn = 8 THEN TestType1a END) Test_Type8,
MAX( CASE WHEN rn = 8 THEN Complete_Date END) Complete_Date8
FROM CTE
GROUP BY SampleNo, PAProjid
ORDER BY SampleNo

You need to complete the query as you missed 2 columns (DateAndTime & Comments1a) and I won't write the 26 column groups.
Read the following article about CROSS TABS http://www.sqlservercentral.com/articles/T-SQL/63681/
and ask any questions that you might have.



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1590463
Posted Wednesday, July 9, 2014 11:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 6:09 AM
Points: 5, Visits: 53
Luis, once again thank you for your help. The revised code works flawlessly in my application now. In a gridview, the TestType is now highlighted in the SampleNo row when the Complete_Date is not NULL. Perfect!


And I did add the DateAndTime, Comments1a fields (I removed them from the test code to abbreviate somewhat).

--Larry
Post #1590892
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse