Rotate Database Table

Question

Imagine I have a database table that has the following format:

╔══════════════════════╗
║ Name   Quarter Sales ║
╠══════════════════════╣
║ Joe    Q1      700   ║
║ Joe    Q2      650   ║
║ Joe    Q3      660   ║
║ Bill   Q1      500   ║
║ Bill   Q2      520   ║
║ Bill   Q3      550   ║
║ Bob    Q2      200   ║
║ Bob    Q3      250   ║
╚══════════════════════╝

I need to pivot it in the format as below. Here each distinct value of quarter becomes a column, and each quarterly sales is the value of the corresponding column for each sales person.

╔══════════════════════╗
║ Name   Q1   Q2   Q3  ║
╠══════════════════════╣
║ Joe    700  650  660 ║
║ Bill   500  520  550 ║
║ Bob         200  250 ║
╚══════════════════════╝

Is this possible with SQL? I am using SQL Server, but prefer a plain SQL if possible.

We do not know the values that go into Quarter column up front i.e. we are not limited to only Q1/Q2/Q3 etc. Let's say the users can enter Q1, Quarter 1, or anything else they like.

Answer 1

I would suggest use PIVOT method instead.

By looking at comment above, you did wish to make the Quarter Q1, Q2... dynamically, as you might no have specific values for Quarter.

Here I have updated my answer, please have a try and see.

Replace PivotSample with you TableName

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

--GET all pivoted column -> [Q1], [Q12]...
DECLARE @pivot_col AS NVARCHAR(MAX)
;WITH distinct_col AS
(
    SELECT DISTINCT [Quarter]
    FROM PivotSample
)
SELECT @pivot_col = COALESCE(@pivot_col + ',', '') + QUOTENAME([Quarter])
FROM distinct_col

--Generate query, to pivot the data given
DECLARE @query AS NVARCHAR(MAX)
SET @query = 
    N'SELECT [Name], ' + @pivot_col + ' ' +
    'INTO ##TEMP_TBL
    FROM PivotSample
    PIVOT
    (
        SUM(Sales)
        FOR [Quarter] IN ( ' + @pivot_col + ' )
    ) AS pvt'

--Execute query, to insert result into ##TEMP_TBL
EXEC sp_executesql @query


--GET all [Quarter] column, to add in ISNULL after PIVOT column -> e.g:  ISNULL([Q1], 0) AS [Q1], ISNULL([Q2], 0) AS [Q2]...
DECLARE @col_name AS NVARCHAR(MAX)
;WITH distinct_col AS
(
    SELECT DISTINCT [Quarter]
    FROM PivotSample
)
SELECT @col_name =
        STUFF(
        (
            SELECT ', ISNULL(' + QUOTENAME([Quarter]) + ', 0) AS ' + QUOTENAME([Quarter])
            FROM distinct_col
            ORDER BY [Quarter]
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
        ,1 , 1, '')

--Generate query_result, to get result from ##TEMP_TBL after add in ISNULL
DECLARE @query_result AS NVARCHAR(MAX)
SET @query_result = 
    N'SELECT [Name], ' + @col_name + ' ' +
    'FROM ##TEMP_TBL
    ORDER BY [Name]'

--Execute query_result, final result shown
EXEC sp_executesql @query_result

Answer 2

You can try to use aggregation condition SUM with CASE WHEN

SELECT Name,
       SUM(CASE WHEN Quarter = 'Q1' THEN Sales ELSE 0 END) 'Q1',
       SUM(CASE WHEN Quarter = 'Q2' THEN Sales ELSE 0 END) 'Q2',
       SUM(CASE WHEN Quarter = 'Q3' THEN Sales ELSE 0 END) 'Q3'
FROM T
GROUP BY Name