Solved

Sql PivotTable help

Posted on 2014-07-30
2
267 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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 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

12 Experts available now in Live!

Get 1:1 Help Now