Solved

Sql PivotTable help

Posted on 2014-07-30
2
288 Views
Last Modified: 2014-07-31
new to pivot tables here.....

here is the query


SELECT TOP (100) PERCENT RTPCRDV.CompanyLabel AS comp, RTPCRDV.CloudGroupLabel AS [Group], RTPCRDV.ClientPanelLabel AS Verb, DATEADD(dd, 0,
               DATEDIFF(dd, 0, RTPCRDV.ReceiptCreated)) AS date, COUNT(*) AS count
FROM  Product INNER JOIN
               RfidTagProductContractReceiptDataView AS RTPCRDV ON Product.Id = RTPCRDV.ProductId INNER JOIN
               ProductShape ON Product.ProductShapeId = ProductShape.Id
GROUP BY RTPCRDV.CompanyLabel, RTPCRDV.CompanyId, RTPCRDV.ClientPanelId, RTPCRDV.ClientPanelLabel, DATEADD(dd, 0, DATEDIFF(dd, 0,
               RTPCRDV.ReceiptCreated)), RTPCRDV.CloudGroupLabel
HAVING (RTPCRDV.CompanyId = 2030) AND (DATEADD(dd, 0, DATEDIFF(dd, 0, RTPCRDV.ReceiptCreated)) > CONVERT(DATETIME, '2014-06-20 00:00:00', 102))
ORDER BY date



here is what it returns


Comp      group              verb          date                                       count
Omni Inc      PrimeTime      Check In      2014-06-23 00:00:00.000      66
Omni Inc      PrimeTime      Drop Off      2014-06-23 00:00:00.000      10
Omni Inc      PrimeTime      Ship      2014-06-23 00:00:00.000      10
Omni Inc      PrimeTime      None      2014-06-24 00:00:00.000      68
Omni Inc      PrimeTime      Check In      2014-06-24 00:00:00.000      65
Omni Inc      PrimeTime      Drop Off      2014-06-24 00:00:00.000      7
Omni Inc      PrimeTime      Pick Up      2014-06-24 00:00:00.000      11
Omni Inc      PrimeTime      Prepare      2014-06-24 00:00:00.000      108
Omni Inc      PrimeTime      Ship      2014-06-24 00:00:00.000      129
Omni Inc      PrimeTime      Return      2014-06-24 00:00:00.000      1
Omni Inc      PrimeTime      Lost      2014-06-24 00:00:00.000      2
Omni Inc      PrimeTime      Check In      2014-06-25 00:00:00.000      2072
Omni Inc      PrimeTime      Drop Off      2014-06-25 00:00:00.000      557
Omni Inc      PrimeTime      Pick Up      2014-06-25 00:00:00.000      1533
Omni Inc      PrimeTime      Prepare      2014-06-25 00:00:00.000      1982
Omni Inc      PrimeTime      Ship      2014-06-25 00:00:00.000      1365
Omni Inc      PrimeTime      Lost      2014-06-25 00:00:00.000      20
Omni Inc      PrimeTime      Check In      2014-06-27 00:00:00.000      506
Omni Inc      PrimeTime      Drop Off      2014-06-27 00:00:00.000      16
Omni Inc      PrimeTime      Prepare      2014-06-27 00:00:00.000      18
Omni Inc      PrimeTime      Ship      2014-06-27 00:00:00.000      17
Omni Inc      PrimeTime      Check In      2014-06-30 00:00:00.000      1650
Omni Inc      PrimeTime      Drop Off      2014-06-30 00:00:00.000      35
Omni Inc      PrimeTime      Pick Up      2014-06-30 00:00:00.000      15
Omni Inc      PrimeTime      Prepare      2014-06-30 00:00:00.000      501
Omni Inc      PrimeTime      Ship      2014-06-30 00:00:00.000      3240
Omni Inc      PrimeTime      Check In      2014-07-01 00:00:00.000      960
Omni Inc      PrimeTime      Drop Off      2014-07-01 00:00:00.000      801
Omni Inc      PrimeTime      Pick Up      2014-07-01 00:00:00.000      480
Omni Inc      PrimeTime      Prepare      2014-07-01 00:00:00.000      480
Omni Inc      PrimeTime      Ship      2014-07-01 00:00:00.000      1060
Omni Inc      PrimeTime      Attach      2014-07-02 00:00:00.000      100
Omni Inc      PrimeTime      Check In      2014-07-02 00:00:00.000      3262
Omni Inc      PrimeTime      Drop Off      2014-07-02 00:00:00.000      821
Omni Inc      PrimeTime      Pick Up      2014-07-02 00:00:00.000      195
Omni Inc      PrimeTime      Prepare      2014-07-02 00:00:00.000      2064
Omni Inc      PrimeTime      Ship      2014-07-02 00:00:00.000      2027
Omni Inc      PrimeTime      Return      2014-07-02 00:00:00.000      20
Omni Inc      2030      26      Check In      7/3/2014      PT      225
Omni Inc      2030      28      Drop Off      7/3/2014      PT      205
Omni Inc      2030      29      Pick Up      7/3/2014      PT      150
Omni Inc      2030      30      Prepare      7/3/2014      PT      231
Omni Inc      2030      31      Ship      7/3/2014      PT      208
Omni Inc      2030      26      Check In      7/7/2014      PT      24
Omni Inc      2030      29      Pick Up      7/7/2014      PT      23
Omni Inc      2030      26      Check In      7/8/2014      PT      12
Omni Inc      2030      3      Attach      7/9/2014      PT      2
Omni Inc      2030      26      Check In      7/9/2014      None      3
Omni Inc      2030      26      Check In      7/9/2014      PT      74
Omni Inc      2030      28      Drop Off      7/9/2014      PT      14
Omni Inc      2030      29      Pick Up      7/9/2014      PT      13
Omni Inc      2030      30      Prepare      7/9/2014      None      2
Omni Inc      2030      30      Prepare      7/9/2014      PT      54
Omni Inc      2030      31      Ship      7/9/2014      PT      51
Omni Inc      2030      43      Return      7/9/2014      PT      8
Omni Inc      2030      28      Drop Off      7/10/2014      PT      7
Omni Inc      2030      26      Check In      7/11/2014      PT      102
Omni Inc      2030      28      Drop Off      7/11/2014      PT      51
Omni Inc      2030      29      Pick Up      7/11/2014      PT      39
Omni Inc      2030      30      Prepare      7/11/2014      PT      90
Omni Inc      2030      31      Ship      7/11/2014      PT      55
Omni Inc      2030      44      Lost      7/11/2014      PT      9
Omni Inc      2030      44      Lost      7/14/2014      PT      276
Omni Inc      2030      26      Check In      7/15/2014      PT      59
Omni Inc      2030      30      Prepare      7/15/2014      PT      85



here is what i am looking for    

grouping by date of each verb and count

something like this

              6/23/2014      6/24/2014      6/25/2014      …      7/11/2014
checkin      66                      65                      2072             …      102
prepare      0                     108                      1982             …         90
ship             10                     129                      1365             …        55
Dropoff      10                    7                      557                     …        51
pickup      0                      11                  1533             …        39
lost            0                    2                     20                 …                9
attach      15                    0                  0                           …      0
return      0                   1                       0            …             0


the order of the row headers on teh right is not important, just that the totals match up down the row.
0
Comment
Question by:CASorter
2 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 200 total points
ID: 40229559
--here are specific examples that you can run in SQL SSMS query to help you write the code:


CREATE TABLE #test_table
        (
        Company  VARCHAR(3),
        Year     SMALLINT,
        Quarter  TINYINT,
        Amount   DECIMAL(2,1),
        Quantity DECIMAL(2,1)
        )
GO
 INSERT INTO #test_table
        (Company,Year, Quarter, Amount, Quantity)
 SELECT 'ABC', 2006, 1, 1.1, 2.2 UNION ALL
 SELECT 'ABC', 2006, 2, 1.2, 2.4 UNION ALL
 SELECT 'ABC', 2006, 3, 1.3, 1.3 UNION ALL
 SELECT 'ABC', 2006, 4, 1.4, 4.2 UNION ALL
 SELECT 'ABC', 2007, 1, 2.1, 2.3 UNION ALL
 SELECT 'ABC', 2007, 2, 2.2, 3.1 UNION ALL
 SELECT 'ABC', 2007, 3, 2.3, 2.1 UNION ALL
 SELECT 'ABC', 2007, 4, 2.4, 1.5 UNION ALL
 SELECT 'ABC', 2008, 1, 1.5, 5.1 UNION ALL
 SELECT 'ABC', 2008, 3, 2.3, 3.3 UNION ALL
 SELECT 'ABC', 2008, 4, 1.9, 4.2 UNION ALL
 SELECT 'XYZ', 2006, 1, 2.1, 3.6 UNION ALL
 SELECT 'XYZ', 2006, 2, 2.2, 1.8 UNION ALL
 SELECT 'XYZ', 2006, 3, 3.3, 2.6 UNION ALL
 SELECT 'XYZ', 2006, 4, 2.4, 3.7 UNION ALL
 SELECT 'XYZ', 2007, 1, 3.1, 1.9 UNION ALL
 SELECT 'XYZ', 2007, 2, 1.2, 1.2 UNION ALL
 SELECT 'XYZ', 2007, 3, 3.3, 4.2 UNION ALL
 SELECT 'XYZ', 2007, 4, 1.4, 4.0 UNION ALL
 SELECT 'XYZ', 2008, 1, 2.5, 3.9 UNION ALL
 SELECT 'XYZ', 2008, 2, 3.5, 2.1 UNION ALL
 SELECT 'XYZ', 2008, 3, 1.3, 3.9 UNION ALL
 SELECT 'XYZ', 2008, 4, 3.9, 3.4
GO

--using crosstab
SELECT Company,
        Year,
        SUM(CASE WHEN Quarter = 1 THEN Amount   ELSE 0 END) AS Q1Amt,
        SUM(CASE WHEN Quarter = 1 THEN Quantity ELSE 0 END) AS Q1Qty,
        SUM(CASE WHEN Quarter = 2 THEN Amount   ELSE 0 END) AS Q2Amt,
        SUM(CASE WHEN Quarter = 2 THEN Quantity ELSE 0 END) AS Q2Qty,
        SUM(CASE WHEN Quarter = 3 THEN Amount   ELSE 0 END) AS Q3Amt,
        SUM(CASE WHEN Quarter = 3 THEN Quantity ELSE 0 END) AS Q3Qty,
        SUM(CASE WHEN Quarter = 4 THEN Amount   ELSE 0 END) AS Q4Amt,
        SUM(CASE WHEN Quarter = 4 THEN Quantity ELSE 0 END) AS Q4Qty,
        SUM(Amount)   AS TotalAmt,
        SUM(Quantity) AS TotalQty
   FROM #test_table
  GROUP BY Company, Year
  ORDER BY Company, Year

-- using pivot

--===== Use a Pivot to do the same thing we did with the Cross Tab
 SELECT Year,             --(4)
        [1] AS [1st Qtr], --(3)
        [2] AS [2nd Qtr],
        [3] AS [3rd Qtr],
        [4] AS [4th Qtr],
        [1]+[2]+[3]+[4] AS Total --(5)
   FROM (SELECT Year, Quarter,Amount FROM #test_table)  AS src --(1)
  PIVOT (SUM(Amount) FOR Quarter IN ([1],[2],[3],[4])) AS pvt --(2)
  ORDER BY Year


--===== Converting NULLs to zero's in the Pivot using COALESCE
 SELECT Year,
        COALESCE([1],0) AS [1st Qtr],
        COALESCE([2],0) AS [2nd Qtr],
        COALESCE([3],0) AS [3rd Qtr],
        COALESCE([4],0) AS [4th Qtr],
        COALESCE([1],0) + COALESCE([2] ,0) + COALESCE([3],0) + COALESCE([4],0) AS Total
   FROM (SELECT Year, Quarter,Amount FROM #test_table)  AS src
  PIVOT (SUM(Amount) FOR Quarter IN ([1],[2],[3],[4])) AS pvt
  ORDER BY Year



--unpivot
create table #TableA (Period sysname, Col1 smallint, Col2 smallint, Col3 smallint)
insert into #tableA select 'Current',50,80,90
select * from #tableA


SELECT Period, Cols, Counts
FROM
(SELECT Period, Col1, Col2, Col3 FROM #tableA) p
UNPIVOT
(counts FOR Cols IN (Col1, Col2, Col3 )
)AS unpvt;
GO
0
 
LVL 14

Accepted Solution

by:
Vikas Garg earned 300 total points
ID: 40231015
Hi,

You can do this using this Query

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
 
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
       + QUOTENAME(Tdate) FROM (SELECT DISTINCT Tdate FROM YourTable) AS Courses
 
SET @DynamicPivotQuery =
  N'SELECT Groups, ' + @ColumnName + '
    FROM YourTable
    PIVOT(sum(income)
          FOR Tdate IN (' + @ColumnName + ')) AS PVTTable'

EXEC sp_executesql @DynamicPivotQuery

Open in new window

0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now