Making a Simple SQL pivot

Question

Example Image here

MSSQL Database

I have looked at many different SQL Pivots but they seem so complicated!

I have a query, that I simply want to pivot on date

so that DATE is on to top

and that the other columns are down the side.

SELECT TOP (1000) [date]
  ,[Total Orders]
  ,[Orders Done]
  ,[Pieces picked]
  ,[Items Picked]
  ,[Average Items on Order]
  ,[Picked Today]

  FROM [Cass_SSRS_DailyMiTable]

That's the query, but I am just struggling to do what I need to do....

Any help would be much appreciated

Currently date is formatting as

 date | total orders |   orders done  |   items picked    |    average items

and i want it like this

                         24/03/2017        25/03/2017       26/03/2017
Total Orders                53                  351              35
Pieces picked               352                 23               55
items picked                 33                 235              25
average items on order      22                   52              95
picked today

Answer 1

You can follow this basic example.

;WITH PreUnpivot AS
(
    SELECT
        date, 
        [Total Orders], 
        [Orders Done], 
        [Items Picked], 
        [Picked Today]
        -- Include other columns you want to display here
    FROM
        Cass_SSRS_DailyMiTable AS C
)
SELECT
    T.*
FROM
    PreUnpivot AS C
    UNPIVOT (
        PivotedValues FOR Concept IN ([Total Orders], [Orders Done], [Items Picked], [Picked Today])
    ) AS T

---

EDIT: You need to do a dynamic pivot (by date) after unpivoting.

IF OBJECT_ID('tempdb..#Cass_SSRS_DailyMiTable') IS NOT NULL
    DROP TABLE #Cass_SSRS_DailyMiTable

CREATE TABLE #Cass_SSRS_DailyMiTable (
    [date] DATE
  ,[Total Orders] INT
  ,[Orders Done] INT
  ,[Pieces picked] INT
  ,[Items Picked] INT
  ,[Average Items on Order] INT
  ,[Picked Today] INT)

INSERT INTO #Cass_SSRS_DailyMiTable (
    date,
    [Total Orders],
    [Pieces picked],
    [Items Picked],
    [Average Items on Order],
    [Picked Today])
VALUES
    ('2017-03-24', 53, 352, 33, 22, 0),
    ('2017-03-25', 351, 23, 235, 52, 0),
    ('2017-03-26', 35, 55, 25, 95, 0)


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT ',' + QUOTENAME(CONVERT(VARCHAR(100), c.date, 120)) 
            FROM #Cass_SSRS_DailyMiTable c
            ORDER BY c.date ASC
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 

    ';WITH PreUnpivot AS
    (
        SELECT
            C.date, 
            C.[Total Orders],
            C.[Pieces picked],
            C.[Items Picked],
            C.[Average Items on Order],
            C.[Picked Today]
        FROM
            #Cass_SSRS_DailyMiTable AS C
    )
    SELECT
        P.Concept,
        ' + @cols + '
    FROM
        PreUnpivot AS C
        UNPIVOT (
            PivotedValues FOR Concept IN ([Total Orders], [Pieces picked], [Items Picked], [Average Items on Order], [Picked Today])
        ) AS T
        PIVOT (
            MAX(T.PivotedValues) FOR T.Date IN (' + @cols + ')
        ) AS P'


EXEC (@query)

Because the dates that will end up as column are dynamic, there is no escaping of Dynamic SQL. If you want less dates to show up, then filter them on the SET @cols = ... with the proper WHERE clause.