Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sql PivotTable help

Posted on 2014-07-30
2
Medium Priority
?
302 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 600 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 15

Accepted Solution

by:
Vikas Garg earned 900 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

719 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