The OrangeGoblin The OrangeGoblin - 3 months ago 18
SQL Question

Transpose rows into columns SQL Server 2014

I have a CSV file with 51 columns.

In this order, there is a UID Column, Serial Column, Date column and 48 columns for each 30 minute segment of the day (from 00:30 through to 00:00). Each day has a new row.
So it looks like:

UID | Serial | Date | Val_0030 | Val_0100 | Val_0130 | ..... | Val_0000
123 | 123456 | 2016-01-02 | 56.2 | 23.25 | 32.8 | ..... | 86.23


I need to transpose this data into 4 columns, so that each half hour has a UID, Serial and Date column. In other words I need to run down instead of across.

To look like this:

UID | Serial | 2016-01-02 00:30 | Value


Rather that each day having a new row as it currently does, I will determine that Val_0130 for example will determine that the time is 01:30 and will concat with the date

I have tried using pivot and unpivot without any success. Can anyone advise the best approach to do this.

Answer

I would use UNPIVOT and then cut up the column name Val_0130 to add to datetime to get the desired result. This way you will only have to write the 48 columns in one spot.

here is some test data:

DECLARE @Table AS TABLE (UID INT, Serial INT, Date DATETIME, Val_0030 MONEY, Val_0100 MONEY, Val_0130 MONEY, Val_0000 MONEY)
INSERT INTO @Table (UID, Serial, Date, Val_0030, Val_0100, Val_0130, Val_0000)
VALUES
(123, 123456, '2016-01-02',56.2,23.25,12.34,86.23)
,(231, 234561, '2016-01-05',26.2,13.25,23.45,106.23)
,(312, 345612, '2016-01-07',76.2,3.25,34.56,1010.56)

And the Query

SELECT
    UID
    ,Serial
    ,DateWithTime = [Date] + CAST((SUBSTRING(ColumnNames,5,2) + ':' + RIGHT(ColumnNames,2)) AS DATETIME)
    ,Value
FROM
    @Table t
    UNPIVOT (
       Value
       FOR ColumnNames IN (Val_0030, VAL_0100, Val_0130, VAL_0000)
    ) u

And if you don't want to type out all 48 columns, like I wouldn't want to, just run this query and copy and past the result into the ColumnNames IN () section of the above query.

DECLARE @ColString VARCHAR(MAX) = ''
DECLARE @DT DATETIME = '00:00'

WHILE @DT < '1900-01-02 00:00:00.000'
BEGIN
    IF LEN(@ColString) > 0
    BEGIN
       SET @ColString += ','
    END

    SET @ColString += 'Val_' + FORMAT(@DT,'HHmm')

    SET @DT = DATEADD(MINUTE,30,@DT)
END

SELECT @ColString
Comments