CODEWITHSUNDEEP
sqlsql-server
jnoguerm
jnoguerm

Reputation: 147

How pivot columns in SQL Server?

I have a table with this values:

Type  |  Value
---------------
  A      String
  A      Int
  B      String
  B      String
  C      Double
  C      Float

and I would like to know the query that returns this:

Type  |  String  |  Int  | Double  |  Float
--------------------------------------------
  A        1         1        0          0
  B        2         0        0          0 
  C        0         0        1          1

How could I do it?

Upvotes: 1

Views: 1644

Answers (3)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use the following solution, using PIVOT:

SELECT Type, [String], [Int], [Double], [Float]  
FROM (
    SELECT Type, Value
    FROM table_name
) st PIVOT (  
    COUNT(Value)
    FOR Value IN ([String], [Int], [Double], [Float])  
) AS pt;

How could I add a column with the sum of the values ​​of each row and a row with the sum of the values ​​of each column?

SELECT Type, [String], [Int], [Double], [Float], TotalRow
FROM (
    SELECT Type, Value, COUNT(Value) OVER (PARTITION BY Type) AS TotalRow
    FROM test
) st PIVOT (  
    COUNT(Value)
    FOR Value IN ([String], [Int], [Double], [Float])  
) pt

UNION ALL

SELECT 'Total', SUM([String]), SUM([Int]), SUM([Double]), SUM([Float]), SUM(TotalRow) 
FROM (
    SELECT Type, [String], [Int], [Double], [Float], TotalRow
    FROM (
        SELECT Type, Value, COUNT(Value) OVER (PARTITION BY Type) AS TotalRow
        FROM test
    ) st PIVOT (  
        COUNT(Value)
        FOR Value IN ([String], [Int], [Double], [Float])  
    ) pt
)t;

demo on dbfiddle.uk

Upvotes: 3

Azeem Hafeez
Azeem Hafeez

Reputation: 250

You can also use Dynamic SQL PIVOT

CREATE TABLE test (
  DataType VARCHAR(10),
  Value VARCHAR(10)
);
INSERT INTO test VALUES
  ('A', 'String'),
  ('A', 'Int'),
  ('B', 'String'),
  ('B', 'String'),
  ('C', 'Double'),
  ('C', 'Float');
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Value) 
            FROM test c
            FOR XML PATH(''), Type
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT DataType, ' + @cols + ' from 
            (
                select DataType,Value
                from test
           ) x
            pivot 
            (
                 count(Value)
                for Value in (' + @cols + ')
            ) p '


execute(@query)

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

You can try to use a conditional aggregated function.

SELECT Type,
       COUNT(CASE WHEN VALUES = 'String' THEN 1 END) 'String',
       COUNT(CASE WHEN VALUES = 'Int' THEN 1 END) 'Int',
       COUNT(CASE WHEN VALUES = 'Double' THEN 1 END) 'Double',
       COUNT(CASE WHEN VALUES = 'Float' THEN 1 END) 'Float'
FROM T
GROUP BY Type

Upvotes: 2

Related Questions