How to change some of the columns in to rows

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

hi there...

try this. to convert columns into rows, you need to use UNPIVOT.

declare @table table(

[Emp_Name] varchar(50),

[MOB] int,

[PHN] int,

[OTH] int

)

insert @table ([Emp_Name], [PHN], [MOB] ,[OTH])

select 'Steve' ,223 ,456 ,895

union all select 'John' ,789 ,789 ,421

union all select 'jack' , null, 252, 252

SELECT [Emp_Name] ,PhoneDesc,PhoneNumbers FROM

(select [Emp_Name], [PHN], [MOB] ,[OTH] From @table ) PIVOT_TABLE

UNPIVOT

( PhoneNumbers for PhoneDesc in ([PHN], [MOB] ,[OTH])) PIVOT_HANDLE

And this seems to a homework to me.. if it were, then me providing code and u submitting it wont help you to learn! 🙁

anyways, hope it helps...

shield_21 Ten Centuries Points: 1033 More actions · Answer 2

Hi..

This may produce your expected result.. I just added logic to concatenate the values from unpivoted table..

DECLARE @table TABLE (Emp_Name VARCHAR(50),PHN INT, MOB INT, OTH INT)

INSERT INTO @table

SELECT 'Steve',223,456,895 UNION ALL

SELECT 'John',789,789,421 UNION ALL

SELECT 'jack',NULL,252,252

SELECTEmp_Name

,temp.NMBR

,STUFF((ISNULL('+' + (MAX(CASE WHEN type='PHN' THEN 'P' END)),'') +

ISNULL('+' + (MAX(CASE WHEN type='MOB' THEN 'M' END)),'') +

ISNULL('+' + (MAX(CASE WHEN type='OTH' THEN 'O' END)),'')),1,1,'') AS [Type]

FROM@table

UNPIVOT(

NMBR FOR [Type] IN (PHN,MOB,OTH)

) temp

GROUP BY Emp_Name,NMBR

ORDER BY Emp_Name

Hope this helps.. 🙂

Cheers!

rbnwork Grasshopper Points: 12 More actions · Answer 3

Thanx very much.. it works.. i could easily change it to my intended code..