Transpose Rows to column
-
Can anyone help me to transpose below result
SVR dates count
SRV1 03/25/2018 1
SRV1 04/06/2018 59
SRV1 04/07/2018 9
SRV1 04/13/2018 1
SRV1 04/15/2018 1
SRV1 04/20/2018 4
SRV1 04/21/2018 2
SRV1 04/27/2018 5
SRV1 04/28/2018 5
SRV1 04/29/2018 1
SRV1 04/30/2018 62Date can be variable and the range of dates are 1 month ( Getdate and getdate -30)
I want the output in below format
Server 04/01/2018 04/02/2018 04/03/2018 ....... 04/30/2018
SRV1 10 0 2 5Thanks
-
DECLARE @t table(Svr varchar(10), Dt date, Cnt smallint);
INSERT @t ( Svr, Dt, Cnt )
VALUES
( 'SRV1', '20180325', 1 )
, ( 'SRV1', '20180406', 59)
, ( 'SRV1', '20180407', 9 )
, ( 'SRV1', '20180413', 1 )
, ( 'SRV1', '20180415', 1 )
, ( 'SRV1', '20180420', 4 )
, ( 'SRV1', '20180421', 2 )
, ( 'SRV1', '20180427', 5 )
, ( 'SRV1', '20180428', 5 )
, ( 'SRV1', '20180429', 1 )
, ( 'SRV1', '20180430', 62);
SELECT
Svr
, [2018-03-25]
, [2018-04-06]
, [2018-04-07]
, [2018-04-13]
, [2018-04-15]
, [2018-04-20]
, [2018-04-21]
, [2018-04-27]
, [2018-04-28]
, [2018-04-29]
, [2018-04-30]
FROM @t t
PIVOT
( Sum(Cnt) FOR Dt IN ( [2018-03-25], [2018-04-06], [2018-04-07], [2018-04-13], [2018-04-15], [2018-04-20], [2018-04-21], [2018-04-27], [2018-04-28], [2018-04-29], [2018-04-30] )
) P;
- sksingh123 - Tuesday, May 1, 2018 2:58 PM
Please see the following articles. The first is on the basics and the second covers how to make it automatically dynamic.
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here is an example of a dynamic cross tab (similar to what's described in Jeff's 2nd link)
IF OBJECT_ID('tempdb..#TestData', 'U') IS NULL
BEGIN -- DROP TABLE #TestData;
CREATE TABLE #TestData (
SRV CHAR(4) NOT NULL,
DateVal DATE NOT NULL,
CountVal INT NOT NULL,
PRIMARY KEY CLUSTERED (SRV, DateVal)
);INSERT #TestData (SRV, DateVal, CountVal) VALUES
( 'SRV1', '20180325', 1 ),
( 'SRV1', '20180406', 59),
( 'SRV1', '20180407', 9 ),
( 'SRV1', '20180413', 1 ),
( 'SRV1', '20180415', 1 ),
( 'SRV1', '20180420', 4 ),
( 'SRV1', '20180421', 2 ),
( 'SRV1', '20180427', 5 ),
( 'SRV1', '20180428', 5 ),
( 'SRV1', '20180429', 1 ),
( 'SRV1', '20180430', 62),( 'SRV2', '20180325', 1 ),
( 'SRV2', '20180406', 22),
( 'SRV2', '20180407', 19 ),
( 'SRV2', '20180413', 12 ),
( 'SRV2', '20180415', 13 ),
( 'SRV2', '20180420', 41 ),
( 'SRV2', '20180421', 8 ),
( 'SRV2', '20180427', 9 ),
( 'SRV2', '20180428', 2 ),
( 'SRV2', '20180429', 9 ),
( 'SRV2', '20180430', 5);
END;--=======================================================================
-- the actual solution starts below...DECLARE
@_begdate DATE = DATEADD(MONTH, -1, GETDATE()),
@_list NVARCHAR(4000) = N'',
@_sql NVARCHAR(4000) = CONCAT(N'SELECT',CHAR(13), CHAR(10), CHAR(9), 'td.SRV'),
@_debug BIT = 0;WITH
cte_n (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_Calendar (dt) AS (
SELECT TOP (DATEDIFF(DAY, @_begdate, GETDATE()))
DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, @_begdate)
FROM
cte_n a CROSS JOIN cte_n b
)
SELECT
@_list = CONCAT(@_list, N',', CHAR(13), CHAR(10), CHAR(9), N'[', c.dt, N'] = SUM(CASE WHEN td.DateVal = ''', c.dt, N''' THEN td.CountVal ELSE 0 END)')FROM
cte_Calendar c;SET @_sql = CONCAT(@_sql, @_list, N'
FROM
#TestData td
GROUP BY
td.SRV;'
);
IF @_debug = 1
BEGIN
print(@_sql);
END;
ELSE
BEGIN
EXEC sys.sp_executesql @_sql;
END; -
Aye. Nicely done, Jason.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Wednesday, May 2, 2018 7:44 AM
Thank you for the kind words Jeff.
I did, however, just notice a potential problem with the code I posted...
The section that builds the @_list value is missing an ORDER BY clause. While it's unlikely that the columns would end up in the incorrect order, it is "possible".
Adding an ORDER BY would guarantee the proper order. So, I'd recommend changing the following section...SELECT
@_list = CONCAT(@_list, N',', CHAR(13), CHAR(10), CHAR(9), N'[', c.dt, N'] = SUM(CASE WHEN td.DateVal = ''', c.dt, N''' THEN td.CountVal ELSE 0 END)')
FROM
cte_Calendar c;to this...
SELECT TOP (10000) -- the TOP clause prevents SQL Server from skipping directly to the last row when the ORDER BY is added...
@_list = CONCAT(@_list, N',', CHAR(13), CHAR(10), CHAR(9), N'[', c.dt, N'] = SUM(CASE WHEN td.DateVal = ''', c.dt, N''' THEN td.CountVal ELSE 0 END)')
FROM
cte_Calendar c
ORDER BY
c.dt ASC;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply