CODEWITHSUNDEEP
sqlsql-servermultidimensional-array
Dan Paschevici
Dan Paschevici

Reputation: 1021

MSSQL Select a 2D array

I would like to know if is there an option to make a select from some tables and the result look like this:

enter image description here

The "Text1", "Text3","Text3" I will take from DB, the SUBTEXT I don't have in DB and I'm supposed to write by my self, the Date also is from DB and the values to fulfill the table.

Upvotes: 0

Views: 969

Answers (1)

Stefan Steiger
Stefan Steiger

Reputation: 82306

You can use PIVOT. The only drawback is you cannot pivot without programming the pivot column names manually (FOR SellDay IN ( [20150101], [20150201] ) ).

;WITH CTE_Products AS 
(
          SELECT 1 AS Product, 'CatFood' AS SubCategory, 'Food' AS Category, 1.0 AS Price   
    UNION SELECT 2 AS Product, 'DogFood' AS SubCategory, 'Food' AS Category, 2.0 AS Price   
    UNION SELECT 3 AS Product, 'HumanFood' AS SubCategory, 'Food' AS Category, 3.0 AS Price   
    UNION SELECT 5 AS Product, 'Windows Software' AS SubCategory, 'Software' AS Category, 4.0 AS Price   
    UNION SELECT 6 AS Product, 'Mac Software' AS SubCategory, 'Software' AS Category, 5.0 AS Price   
    UNION SELECT 4 AS Product, 'Linux Software' AS SubCategory, 'Software' AS Category, 0.0 AS Price   
) 
,CTE_SALES AS 
(
          SELECT 1 AS SalesProduct, CAST('20150101' AS datetime) AS SellDay, 3 AS Quantity 
    UNION SELECT 2 AS SalesProduct, CAST('20150101' AS datetime) AS SellDay, 6 AS Quantity 
    UNION SELECT 3 AS SalesProduct, CAST('20150101' AS datetime) AS SellDay, 9 AS Quantity 
    UNION SELECT 4 AS SalesProduct, CAST('20150101' AS datetime) AS SellDay, 9 AS Quantity 
    UNION SELECT 5 AS SalesProduct, CAST('20150101' AS datetime) AS SellDay, 9 AS Quantity 
    UNION SELECT 6 AS SalesProduct, CAST('20150101' AS datetime) AS SellDay, 9 AS Quantity 

    UNION SELECT 1 AS SalesProduct, CAST('20150201' AS datetime) AS SellDay, 3 AS Quantity 
    UNION SELECT 2 AS SalesProduct, CAST('20150201' AS datetime) AS SellDay, 6 AS Quantity 
    UNION SELECT 3 AS SalesProduct, CAST('20150201' AS datetime) AS SellDay, 9 AS Quantity 
    UNION SELECT 4 AS SalesProduct, CAST('20150201' AS datetime) AS SellDay, 9 AS Quantity 
    UNION SELECT 5 AS SalesProduct, CAST('20150201' AS datetime) AS SellDay, 9 AS Quantity 
    UNION SELECT 6 AS SalesProduct, CAST('20150201' AS datetime) AS SellDay, 9 AS Quantity 
) 
SELECT * 
FROM
(
    SELECT 
         Category
        ,SubCategory
        ,SellDay
        ,Quantity * Price AS Value 
    FROM CTE_Products 

    LEFT JOIN CTE_SALES
        ON SalesProduct = Product 

) AS SourceTable
PIVOT
(
    SUM(Value)
    FOR SellDay IN ( [20150101], [20150201] )
) AS PivotTable

You can also do it half-dynamically, like this:

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

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



DECLARE @cols nvarchar(MAX) 

;WITH CTE_Products AS 
(
          SELECT 1 AS Product, 'CatFood' AS SubCategory, 'Food' AS Category, 1.0 AS Price   
    UNION SELECT 2 AS Product, 'DogFood' AS SubCategory, 'Food' AS Category, 2.0 AS Price   
    UNION SELECT 3 AS Product, 'HumanFood' AS SubCategory, 'Food' AS Category, 3.0 AS Price   
    UNION SELECT 5 AS Product, 'Windows Software' AS SubCategory, 'Software' AS Category, 4.0 AS Price   
    UNION SELECT 6 AS Product, 'Mac Software' AS SubCategory, 'Software' AS Category, 5.0 AS Price   
    UNION SELECT 4 AS Product, 'Linux Software' AS SubCategory, 'Software' AS Category, 0.0 AS Price   
) 
,CTE_SALES AS 
(
          SELECT 1 AS SalesProduct, CAST('20150101' AS datetime) AS SellDay, 3 AS Quantity 
    UNION SELECT 2 AS SalesProduct, CAST('20150101' AS datetime) AS SellDay, 6 AS Quantity 
    UNION SELECT 3 AS SalesProduct, CAST('20150101' AS datetime) AS SellDay, 9 AS Quantity 
    UNION SELECT 4 AS SalesProduct, CAST('20150101' AS datetime) AS SellDay, 9 AS Quantity 
    UNION SELECT 5 AS SalesProduct, CAST('20150101' AS datetime) AS SellDay, 9 AS Quantity 
    UNION SELECT 6 AS SalesProduct, CAST('20150101' AS datetime) AS SellDay, 9 AS Quantity 

    UNION SELECT 1 AS SalesProduct, CAST('20150201' AS datetime) AS SellDay, 3 AS Quantity 
    UNION SELECT 2 AS SalesProduct, CAST('20150201' AS datetime) AS SellDay, 6 AS Quantity 
    UNION SELECT 3 AS SalesProduct, CAST('20150201' AS datetime) AS SellDay, 9 AS Quantity 
    UNION SELECT 4 AS SalesProduct, CAST('20150201' AS datetime) AS SellDay, 9 AS Quantity 
    UNION SELECT 5 AS SalesProduct, CAST('20150201' AS datetime) AS SellDay, 9 AS Quantity 
    UNION SELECT 6 AS SalesProduct, CAST('20150201' AS datetime) AS SellDay, 9 AS Quantity 
) 
SELECT 
     Category
    ,SubCategory
    ,SellDay
    ,Quantity * Price AS Value 
INTO #MySales 
FROM CTE_Products 

LEFT JOIN CTE_SALES
     ON SalesProduct = Product 





SELECT  
    @cols = COALESCE(@cols + N', ', N'') + N'[' + CONVERT(nchar(8), SellDay, 112) + N']' 
FROM #MySales 
GROUP BY SellDay  
ORDER BY SellDay ASC -- DESC ? 


SET @cols = N'
SELECT * 
INTO ##MyPivottedResults 
FROM #MySales
PIVOT(SUM(Value) 
FOR SellDay IN (' + @cols + ')) AS PivotTable 
'

-- PRINT @cols 
EXECUTE(@cols) 

SELECT * 
FROM ##MyPivottedResults 



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

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

Upvotes: 1

Related Questions