RE: Vertical to Horizontal – SQLServerCentral

One Orange Chip

Points: 26944

March 29, 2012 at 1:42 am

Try this

DECLARE @DetailTable TABLE(Store INT, PhoneNr VARCHAR(30))

INSERT INTO @DetailTable(Store, PhoneNr)

SELECT 4711, '49911-1111111' UNION ALL

SELECT 4711, '49911-1111112' UNION ALL

SELECT 4711, '49911-1111113';

WITH CTE AS (

SELECT Store, PhoneNr,

ROW_NUMBER() OVER(PARTITION BY Store ORDER BY PhoneNr) AS rn

FROM @DetailTable)

SELECT Store,

MAX(CASE WHEN rn=1 THEN PhoneNr END) AS Phone1,

MAX(CASE WHEN rn=2 THEN PhoneNr END) AS Phone2,

MAX(CASE WHEN rn=3 THEN PhoneNr END) AS Phone3

FROM CTE

GROUP BY Store;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum