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?