Rows into Columns

[RJL1]

I have a query that return a list of carrier by date

DECLARE @STARTDATE   DATETIME
DECLARE @ENDDATE     DATETIME

SET @STARTDATE = '03/01/2011'
SET @ENDDATE   = '03/01/2011'
-----------------------------------------------------------
SELECT
------------------- SHIPMENT HEADER  -----------------------
'ACTUAL_SHIP_DATE_TIME' = CONVERT (VARCHAR(30),SH.ACTUAL_SHIP_DATE_TIME,101),
SH.WAREHOUSE,
SH.COMPANY,
SH.CARRIER
------------------------------------------------------------
FROM
SHIPMENT_HEADER SH WITH(NOLOCK)
------------------------------------------------------------
WHERE
SH.ACTUAL_SHIP_DATE_TIME IS NOT NULL
AND SH.COMPANY   = '317'
AND SH.WAREHOUSE = '029'
AND CONVERT (VARCHAR(30),SH.ACTUAL_SHIP_DATE_TIME,101)
    BETWEEN @STARTDATE AND @ENDDATE
------------------------------------------------------------

Query Results

DATE         WAREHOUSE   COMPANY    CARREIR

03/01/2011   29          317        CPUX
03/01/2011   29          317        CPUX
03/01/2011   29          317        PRONTO
03/01/2011   29          317        PRONTO
03/01/2011   29          317        CPUX
03/01/2011   29          317        FEDEX
03/01/2011   29          317        FEDEX
03/01/2011   29          317        FEDEX
03/01/2011   29          317        FEDEX
03/01/2011   29          317        GROUND

This would sum up to

GROUND = 1
PRONTO = 2
CPUX = 3
FEDEX= 4

I need to display this as

DATE         WAREHOUSE   COMPANY  GROUND PRONTO CPUX FEDEX
   
03/01/2011   29          317      1      2      3    4

What would be the best way to accomplish this with a list of carreir that can change fron day to day. Some day we could have 4 carreir some day 5-6.

Thanks
RJL

 

[sparkbyte]

Wtih a PIVOT query.

http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx

I haven't ued PIVOT's much so I will have to work with it a little before i could show an example. So I have posted a link to tutorial.


Thanks

John Fuhrman

 

[markros]

If you don't know your types in advance, then you're talking about dynamic PIVOT. Otherwise static pivot.

I also want to point out the problem with your date checks in the original query. Using CONVERT function makes your query non-sargable.

Rather than fixing the query for you, I'm giving you link by Aaron Bertrand that explains the date related queries

Bad habits to kick : mis-handling date / range queries


PluralSight Learning Library