none
pivot data to create report in sp

    질문

  •  How can I pivot this query to make an output like this?

                                    (shipdates across top)

               

    Mfgsite                   2018-05--01    2018-05--02    2018-05--03   ....
         200
    order
          P543
    item
           z88sa                   1                                                2
         600
           order
           A431
           item
            mw32                                            1

      I want to list the each mfgsite, order and item where I find shippedontime criteria and put a count missed on that
    shipdate. On shipdate 5-1 I find order p543 for item z88sa and it had 1 not shipped on time.

    SELECT ShipDate,OrderNbrLine,ShipSite,MfgSite,PartNbr
    FROM            dbo.Shipments
    where 
    datepart(yyyy,ShipDate) = DATEPART(yyyy,getdate()) and
    datepart(mm,ShipDate) = DATEPART(mm,getdate())
    and
    ShippedOnTime = '0'
     Thanks.



    2018년 5월 18일 금요일 오전 10:21

답변

  • Hi ?!?,

    Thanks for posting the query to create the table and to insert sample data. This is very helpful :-)

    But you still did not provide the third point which I asked you:
    3) The desired result given the sample, as text or image of excel for example.

    It is much faster to help someone who give the proper information and ask the question in the right way. We are a bit in a "guessing game" without the full information and it make people spend more time for no reason.

    I will try my guess as I think you want something a bit different...

    According to your original post you asked to get in the result the columns: mfgsite, ordernbrline,Item
    In addition you now mention that you want to get the column "Item"
    Your entire request is not clear. The logic you are looking for... which column is used for the aggregate and which for the value, but I try to guess according to the information I have

    If you aggregate by column "Item" then you will not get it as a column in the result. Basically the pivot query aggregate by all the columns that are not in the aggregate function ("item" in your query) and are not in the column with the value ("shipdate" in your case), meaning: mfgsite, ordernbrline,shipsite

    Maybe you meant to aggregate by the shipsite?!?

    Check if this is what you need, and if you still did not get what you need THEN PLEASE POST THE MISSING INFORMATION:

    SELECT mfgsite, ordernbrline,Item, [2018-05-01],[2018-05-02],[2018-05-03],[2018-05-04]
    FROM(
    	SELECT mfgsite, ordernbrline,shipsite,Item, shipdate
    	FROM TblPivot
    ) AS P
    PIVOT(
    	COUNT(shipsite) 
    	FOR shipdate IN ([2018-05-01],[2018-05-02],[2018-05-03],[2018-05-04])
    ) AS pv

    If the above is what you need to get dynamically, then this is your dynamic query:

    -- Creating Column Names for Pivot
    DECLARE @shipments varchar(200)
    ;with MyCTE as(
    	SELECT DISTINCT '[' + shipdate + ']' Shipments
    	FROM TblPivot
    )
    SELECT @shipments = COALESCE(@shipments + ',', '') + Shipments
    FROM MyCTE
    --select @shipments
    DECLARE @sql varchar(MAX)
    SET @sql = 
    '
    SELECT mfgsite, ordernbrline,Item, '+@shipments+'
    FROM(
    	SELECT mfgsite, ordernbrline,shipsite,Item, shipdate
    	FROM TblPivot
    ) AS P
    PIVOT(
    	COUNT(shipsite) 
    	FOR shipdate IN ('+@shipments+')
    ) AS pv
    '
    print (@sql)
    /*
    SELECT mfgsite, ordernbrline,Item, [2018-05-01],[2018-05-02],[2018-05-03],[2018-05-04]
    FROM(
    	SELECT mfgsite, ordernbrline,shipsite,Item, shipdate
    	FROM TblPivot
    ) AS P
    PIVOT(
    	COUNT(shipsite) 
    	FOR shipdate IN ([2018-05-01],[2018-05-02],[2018-05-03],[2018-05-04])
    ) AS pv
    */
    -- If the pring OK, then we can execute
    exec (@sql)

     

    and the result is like this:


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]
    • 편집됨 pituachMVP, Moderator 2018년 5월 20일 일요일 오후 7:36
    • 답변으로 표시됨 hart60 2018년 5월 21일 월요일 오후 6:28
    2018년 5월 20일 일요일 오후 5:43
    중재자
  •  How can you make sure the shipdates are sorted in pivot? 5-1 to current.

     Thanks.

    FOr that you need to preserve them as dates. Currently you're storing it as of varchar type

    to maintain order do like this

    drop table #TblPivot;
    drop table #shipdates;
    
    
    CREATE TABLE #TblPivot
    (
    Seq int not null identity(1,1),
    mfgsite int,
    OrdernbrLine nvarchar(25),
    ShipSite varchar(25),
    Item varchar(25),
    ShipDate nvarchar(12)
    )
     
    GO
    
    INSERT INTO #TblPivot
    SELECT 200,'1111','250','xx2z','2018-05-01'
    UNION ALL
    SELECT 202,'1112','251','zz3z','2018-05-02'
    UNION ALL
    SELECT 203,'1113','252','xx4z','2018-05-03'
    UNION ALL
    SELECT 203,'1114','253','zz5z','2018-05-04'
    
     GO
    DECLARE @shipments varchar(200)
    DECLARE @sql varchar(1000)
     
    CREATE TABLE #shipdates
    (
    shipments date
    )
     
    INSERT INTO #shipdates (shipments)
    SELECT
     DISTINCT  shipdate 
    FROM #TblPivot
     
    -- Creating Column Names for Pivot
    SELECT @shipments = COALESCE(@shipments + ',', '') + '[' + 
    CONVERT(varchar(10),Shipments,120) + ']'
    FROM #shipdates
    ORDER BY Shipments 
     
    
     
    SET @sql = 
    '
     SELECT
     *
      FROM
      (
      SELECT 
       mfgsite, 
       ordernbrline,
       shipsite,
       Item, 
       shipdate
      FROM #TblPivot
      ) AS P
     PIVOT
     (
       Count(Item) FOR shipdate IN ('+@shipments+')
     ) AS pv
    '
    EXEC (@sql)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • 편집됨 Visakh16MVP 2018년 5월 21일 월요일 오전 11:13
    • 답변으로 표시됨 hart60 2018년 5월 21일 월요일 오후 6:28
    2018년 5월 21일 월요일 오전 11:11

모든 응답

  • Sorry didnt understand much on your data

    Can you post how the data is coming for your query posted above?

    Anyways your output format looks like what you need is a stepped report which can be very easily generated using SSRS

    https://sqldusty.com/2011/07/23/creating-stepped-reports-with-ssrs-2008/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오후 1:23
  •  I was trying to just use the Pivot option and send results of the returned query to Mail.

     Thanks.

    2018년 5월 18일 금요일 오후 6:45
  •  I was trying to just use the Pivot option and send results of the returned query to Mail.

     Thanks.

    Hi,

    Can you please post:

    1) Queries to CREATE your table
    2) Queries  to INSERT sample data.
    3) The desired result given the sample, as text or image of excel for example.
    4) Which version of SQL Server you are using (this will help to fit the query to your version).


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    2018년 5월 18일 금요일 오후 7:01
    중재자
  •  I was trying to just use the Pivot option and send results of the returned query to Mail.

     Thanks.

    Show us the query result atleast without which it hard to understand which field contains which values

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오후 7:03
  • Hi,

    Please refer below link and solution posted few days ago:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc90660d-1027-4981-9dfd-d906b09a9898/sending-a-dynamic-pivot-table-result-by-email-via-sql?forum=transactsql

    Thanks,

    Alambir

    Please vote this as helpful or Mark this as Answered if this helps


    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!

    2018년 5월 18일 금요일 오후 7:10

  •   I'm getting this to work, but is there a way to have it also show the item in display?

     example output wanted using first record

     

    mfgsite ordernbrline mfgsite                                               2018-05-01    2018-05-02 2018-05-03 2018-05-4
    200        1111             250                                                                                  
      Item
          xx2z                                                                                     1                     0              0                0                                                                             

    drop table #TblPivot;
    drop table #shipdates;
    
    
    CREATE TABLE #TblPivot
    (
    Seq int not null identity(1,1),
    mfgsite int,
    OrdernbrLine nvarchar(25),
    ShipSite varchar(25),
    Item varchar(25),
    ShipDate nvarchar(12)
    )
     
    GO
    
    INSERT INTO #TblPivot
    SELECT 200,'1111','250','xx2z','2018-05-01'
    UNION ALL
    SELECT 202,'1112','251','zz3z','2018-05-02'
    UNION ALL
    SELECT 203,'1113','252','xx4z','2018-05-03'
    UNION ALL
    SELECT 203,'1114','253','zz5z','2018-05-04'
    
     GO
    DECLARE @shipments varchar(200)
    DECLARE @sql varchar(1000)
     
    CREATE TABLE #shipdates
    (
    shipments nvarchar(500)
    )
     
    INSERT INTO #shipdates (shipments)
    SELECT
     DISTINCT '[' + shipdate + ']'
    FROM #TblPivot
     
    -- Creating Column Names for Pivot
    SELECT @shipments = COALESCE(@shipments + ',', '') +
    Shipments
    FROM #shipdates
     
    
     
    SET @sql = 
    '
     SELECT
     *
      FROM
      (
      SELECT 
       mfgsite, 
       ordernbrline,
       shipsite,
       Item, 
       shipdate
      FROM #TblPivot
      ) AS P
     PIVOT
     (
       Count(Item) FOR shipdate IN ('+@shipments+')
     ) AS pv
    '
    EXEC (@sql)
    

    2018년 5월 20일 일요일 오전 11:59
  • yes

    Just use  max instead of count inside pivot 

    i.e like

    drop table #TblPivot;
    drop table #shipdates;
    
    
    CREATE TABLE #TblPivot
    (
    Seq int not null identity(1,1),
    mfgsite int,
    OrdernbrLine nvarchar(25),
    ShipSite varchar(25),
    Item varchar(25),
    ShipDate nvarchar(12)
    )
     
    GO
    
    INSERT INTO #TblPivot
    SELECT 200,'1111','250','xx2z','2018-05-01'
    UNION ALL
    SELECT 202,'1112','251','zz3z','2018-05-02'
    UNION ALL
    SELECT 203,'1113','252','xx4z','2018-05-03'
    UNION ALL
    SELECT 203,'1114','253','zz5z','2018-05-04'
    
     GO
    DECLARE @shipments varchar(200)
    DECLARE @sql varchar(1000)
     
    CREATE TABLE #shipdates
    (
    shipments nvarchar(500)
    )
     
    INSERT INTO #shipdates (shipments)
    SELECT
     DISTINCT '[' + shipdate + ']'
    FROM #TblPivot
     
    -- Creating Column Names for Pivot
    SELECT @shipments = COALESCE(@shipments + ',', '') +
    Shipments
    FROM #shipdates
     
    
     
    SET @sql = 
    '
     SELECT
     *
      FROM
      (
      SELECT 
       mfgsite, 
       ordernbrline,
       shipsite,
       Item, 
       shipdate
      FROM #TblPivot
      ) AS P
     PIVOT
     (
       MAX(Item) FOR shipdate IN ('+@shipments+')
     ) AS pv
    '
    EXEC (@sql)

    Output below

    mfgsite	ordernbrline	shipsite	2018-05-01	2018-05-02	2018-05-03	2018-05-04
    -------------------------------------------------------------------------------------------------
    200	1111	250	xx2z	NULL	NULL	NULL
    202	1112	251	NULL	zz3z	NULL	NULL
    203	1113	252	NULL	NULL	xx4z	NULL
    203	1114	253	NULL	NULL	NULL	zz5z


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 20일 일요일 오후 3:24
  • Hi ?!?,

    Thanks for posting the query to create the table and to insert sample data. This is very helpful :-)

    But you still did not provide the third point which I asked you:
    3) The desired result given the sample, as text or image of excel for example.

    It is much faster to help someone who give the proper information and ask the question in the right way. We are a bit in a "guessing game" without the full information and it make people spend more time for no reason.

    I will try my guess as I think you want something a bit different...

    According to your original post you asked to get in the result the columns: mfgsite, ordernbrline,Item
    In addition you now mention that you want to get the column "Item"
    Your entire request is not clear. The logic you are looking for... which column is used for the aggregate and which for the value, but I try to guess according to the information I have

    If you aggregate by column "Item" then you will not get it as a column in the result. Basically the pivot query aggregate by all the columns that are not in the aggregate function ("item" in your query) and are not in the column with the value ("shipdate" in your case), meaning: mfgsite, ordernbrline,shipsite

    Maybe you meant to aggregate by the shipsite?!?

    Check if this is what you need, and if you still did not get what you need THEN PLEASE POST THE MISSING INFORMATION:

    SELECT mfgsite, ordernbrline,Item, [2018-05-01],[2018-05-02],[2018-05-03],[2018-05-04]
    FROM(
    	SELECT mfgsite, ordernbrline,shipsite,Item, shipdate
    	FROM TblPivot
    ) AS P
    PIVOT(
    	COUNT(shipsite) 
    	FOR shipdate IN ([2018-05-01],[2018-05-02],[2018-05-03],[2018-05-04])
    ) AS pv

    If the above is what you need to get dynamically, then this is your dynamic query:

    -- Creating Column Names for Pivot
    DECLARE @shipments varchar(200)
    ;with MyCTE as(
    	SELECT DISTINCT '[' + shipdate + ']' Shipments
    	FROM TblPivot
    )
    SELECT @shipments = COALESCE(@shipments + ',', '') + Shipments
    FROM MyCTE
    --select @shipments
    DECLARE @sql varchar(MAX)
    SET @sql = 
    '
    SELECT mfgsite, ordernbrline,Item, '+@shipments+'
    FROM(
    	SELECT mfgsite, ordernbrline,shipsite,Item, shipdate
    	FROM TblPivot
    ) AS P
    PIVOT(
    	COUNT(shipsite) 
    	FOR shipdate IN ('+@shipments+')
    ) AS pv
    '
    print (@sql)
    /*
    SELECT mfgsite, ordernbrline,Item, [2018-05-01],[2018-05-02],[2018-05-03],[2018-05-04]
    FROM(
    	SELECT mfgsite, ordernbrline,shipsite,Item, shipdate
    	FROM TblPivot
    ) AS P
    PIVOT(
    	COUNT(shipsite) 
    	FOR shipdate IN ([2018-05-01],[2018-05-02],[2018-05-03],[2018-05-04])
    ) AS pv
    */
    -- If the pring OK, then we can execute
    exec (@sql)

     

    and the result is like this:


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]
    • 편집됨 pituachMVP, Moderator 2018년 5월 20일 일요일 오후 7:36
    • 답변으로 표시됨 hart60 2018년 5월 21일 월요일 오후 6:28
    2018년 5월 20일 일요일 오후 5:43
    중재자
  •  How can you make sure the shipdates are sorted in pivot? 5-1 to current.

     Thanks.

    2018년 5월 21일 월요일 오전 9:16
  •  How can you make sure the shipdates are sorted in pivot? 5-1 to current.

     Thanks.

    Like any query, you sort the order by usong "order by" and use the column that you want to sort by it. In your case in the first query where I get the value of @shipments you need to add "order by"

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    2018년 5월 21일 월요일 오전 10:38
    중재자
  •  How can you make sure the shipdates are sorted in pivot? 5-1 to current.

     Thanks.

    FOr that you need to preserve them as dates. Currently you're storing it as of varchar type

    to maintain order do like this

    drop table #TblPivot;
    drop table #shipdates;
    
    
    CREATE TABLE #TblPivot
    (
    Seq int not null identity(1,1),
    mfgsite int,
    OrdernbrLine nvarchar(25),
    ShipSite varchar(25),
    Item varchar(25),
    ShipDate nvarchar(12)
    )
     
    GO
    
    INSERT INTO #TblPivot
    SELECT 200,'1111','250','xx2z','2018-05-01'
    UNION ALL
    SELECT 202,'1112','251','zz3z','2018-05-02'
    UNION ALL
    SELECT 203,'1113','252','xx4z','2018-05-03'
    UNION ALL
    SELECT 203,'1114','253','zz5z','2018-05-04'
    
     GO
    DECLARE @shipments varchar(200)
    DECLARE @sql varchar(1000)
     
    CREATE TABLE #shipdates
    (
    shipments date
    )
     
    INSERT INTO #shipdates (shipments)
    SELECT
     DISTINCT  shipdate 
    FROM #TblPivot
     
    -- Creating Column Names for Pivot
    SELECT @shipments = COALESCE(@shipments + ',', '') + '[' + 
    CONVERT(varchar(10),Shipments,120) + ']'
    FROM #shipdates
    ORDER BY Shipments 
     
    
     
    SET @sql = 
    '
     SELECT
     *
      FROM
      (
      SELECT 
       mfgsite, 
       ordernbrline,
       shipsite,
       Item, 
       shipdate
      FROM #TblPivot
      ) AS P
     PIVOT
     (
       Count(Item) FOR shipdate IN ('+@shipments+')
     ) AS pv
    '
    EXEC (@sql)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • 편집됨 Visakh16MVP 2018년 5월 21일 월요일 오전 11:13
    • 답변으로 표시됨 hart60 2018년 5월 21일 월요일 오후 6:28
    2018년 5월 21일 월요일 오전 11:11
  •  Worked Great!!! Thanks too all who replied..

    2018년 5월 21일 월요일 오후 6:28
  •  Worked Great!!! Thanks too all who replied..

    Cool then :)

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 21일 월요일 오후 6:43
  •  Worked Great!!! Thanks too all who replied..

    You are most welcome :-)

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    2018년 5월 21일 월요일 오후 9:20
    중재자