0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for pro webmasters

-- SO3241450

CREATE TABLE #SO3241450 (
master_customer_id varchar(100)
,demographic_code  varchar(100)
    ,DayOfWeek int NOT NULL
    ,FromTime time NOT NULL
    ,ToTime time NOT NULL
)

INSERT INTO #SO3241450 VALUES
('C45327','TRADES', 1, '10:00', '14:00')
,('C45327','TRADES', 2, '10:00', '14:00')
,('C45327','TRADES', 3, '08:00', '13:00')
,('C45327','TRADES', 4, '09:00', '13:00')
,('C45327','TRADES', 5, '14:00', '22:00')


;WITH Base AS (
    SELECT master_customer_id,DEMOGRAPHIC_CODE, DayOfWeek, FromTime AS [Start], ToTime AS [End]
    FROM #SO3241450
)
,norm AS (
SELECT master_customer_id,DEMOGRAPHIC_CODE, ColName + CONVERT(varchar, DayOfWeek) AS ColName, ColValue
FROM Base
UNPIVOT (ColValue FOR ColName IN ([Start], [End])) AS pvt
)
SELECT *
FROM norm
PIVOT (MIN(ColValue) FOR ColName IN ([Start1], [End1], [Start2], [End2], [Start3], [End3], [Start4], [End4], [Start5], [End5], [Start6], [End6], [Start7], [End7])) AS pvt

fork query

edited apr 4 13 anonymous

created jul 13 10 Cade Roux