SQL Server transposing rows to columns

[UncleVince]

Hi all,

I need to transpose rows to columns to display data in different dimensions or meet table format requirements using SQL server.

Starting from this query and this return

SELECT 
  _p,
  _t,
  _x,
 COUNT (*) _q 
FROM
	[dbo].[subset_data] 
WHERE
	CONVERT ( VARCHAR ( 10 ), data, 120 ) = CONVERT ( VARCHAR ( 10 ), GETDATE( ), 120 ) 
	GROUP BY
    _p,
	_t,
	_x;


+--------+--------------+----------+--------+
| _p     | _t           |   _x     |   _q   |
+--------+--------------+----------+--------+
| 001	 | 06:00-12:00  |   15     |    1   |
| 001	 | 12:00-18:00  |   15	   |    1   |
| 008	 | 06:00-12:00  |    5	   |    2   |
| 009    | 12:00-18:00  |    6	   |    1   |
| 010    | 06:00-12:00  |   12     |    1   |
+--------+--------------+----------+--------+

I have to get this return

+--------+--------------+-------------+-------------+-------------+------------+
| _p     | 00:00-06:00  | 06:00-12:00 | 12:00-18:00 | 18:00-23:59 |    _x      |
+--------+--------------+-------------+-------------+-------------+------------+
| 001	 |      0       |      1      |      1      |      0      |    15      |
| 008	 |      0       |      2      |      0      |      0      |     5      |
| 009	 |      0       |      0      |      1      |      0      |     6      |
| 010	 |      0       |      1      |      0      |      0      |    12      |
+--------+--------------+-------------+-------------+-------------+------------+

Where the row values of column _t are to be converted to columns and for each value of the _p column I have to retrieve the value of the row from _q column.

Can you help me?

[DaveMaxwell]

You need to use PIVOT to do that.

Here’s a good article which explains it well, and the section called “Example of a Dynamic Pivot” is what you’ll need, but the whole article is a good read.

SQL PIVOT and SQL UNPIVOT Examples to Transform Data

[UncleVince]

Thanks a lot for this suggestion

SQLFiddle

-- INIT database
CREATE TABLE subset_data (
   data DATETIME,
  _p VARCHAR(100),
  _t VARCHAR(100),
  _x VARCHAR(100),
);

INSERT INTO subset_data(data, _p, _t, _x) VALUES (GETDATE(), '001', '06:00-12:00', '15');
INSERT INTO subset_data(data, _p, _t, _x) VALUES (GETDATE(), '001', '12:00-18:00', '15' );
INSERT INTO subset_data(data, _p, _t, _x) VALUES (GETDATE(), '008', '06:00-12:00', '5' );
INSERT INTO subset_data(data, _p, _t, _x) VALUES (GETDATE(), '008', '06:00-12:00', '5' );
INSERT INTO subset_data(data, _p, _t, _x) VALUES (GETDATE(), '009', '12:00-18:00', '6');
INSERT INTO subset_data(data, _p, _t, _x) VALUES (GETDATE(), '010', '06:00-12:00', '12');

-- FIRST method
SELECT
	_p,
	[00:00-06:00],
	[06:00-12:00],
	[12:00-18:00],
	[18:00-23:59],
	_x 
FROM
	( SELECT _p, _t, _x FROM [dbo].[subset_data] ) d PIVOT ( COUNT ( _t ) FOR _t IN ( [00:00-06:00], [06:00-12:00], [12:00-18:00], [18:00-23:59] ) ) piv 
ORDER BY
	_p;

-- SECOND method	
SELECT
	_p,
	COUNT ( CASE _t WHEN '00:00-06:00' THEN 1 END ) AS [00:00-06:00],
	COUNT ( CASE _t WHEN '06:00-12:00' THEN 1 END ) AS [06:00-12:00],
	COUNT ( CASE _t WHEN '12:00-18:00' THEN 1 END ) AS [12:00-18:00],
	COUNT ( CASE _t WHEN '18:00-23:59' THEN 1 END ) AS [18:00-23:59],
	_x 
FROM
	dbo.subset_data sd 
GROUP BY
	_p,
	_x 
ORDER BY
	_p;