Transpose Row into columns

Question

Post reply

Transpose Row into columns

naseer78_pk

Mr or Mrs. 500

Points: 592
More actions
March 20, 2012 at 1:57 am

#253307

HI,
I have a phoneDetail Table of my customers
CustomerIDRowPhone Type DefaultFlgContactPersonContactNumber
1 1CompanyPhone1 Richard 1876-926-04545
1 2Home Phone0 Richard 1876 946 04789
1 3Mobile 0 Richard 495 6764/882222
1 4Fax 0 Richard 1876-929-1885
1 5Fax 0 Richard 1876-882-555
1 6Fax 0 Richard 1876-960 666
I need output:
CustomerIDContactPersonCompanyPhone Home Phone Mobile Fax DefaultFlg
1 Richard 1876-926-0905 1876 946 0444495 6764/88274711876-929-1485 1
1 Richard NULL NULL NULL 1876-882-7471 0
1 Richard NULL NULL NULL 1876-960 3882 0
Thanks in Advance
Regards,
NA
March 20, 2012 at 2:31 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

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

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 1

CustomerID ContactPerson CompanyPhone Home Phone Mobile Fax DefaultFlg

1 Richard 1876-926-0905 1876 946 0444 495 6764/8827471 1876-929-1485 1

1 Richard NULL NULL NULL 1876-882-7471 0

1 Richard NULL NULL NULL 1876-960 3882 0

From this o/p first row how will you find the DefaultFlg(default phone)

Regards

Guru

naseer78_pk Mr or Mrs. 500 Points: 592 More actions · Answer 2

I tried

select *

from sbtrading.dbo.Customer_PhoneDetails

where customerid = 1

SELECT CustomerID,

[1],[2],[3],[4]

FROM

(SELECT CustomerID,??,ContactNumber

FROM sbtrading.dbo.Customer_PhoneDetails

WHERE CustomerId = 1

)SourceTable

PIVOT

(

Min(ContactNumber)

FOR ?? IN (1,2,3,4)

) pivottable

BUT GET ONLY 1 ROW NOT GETTING OTHER TWO ROWS

REGARDS,

NA

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

See if this helps

DECLARE @phoneDetail TABLE(CustomerID INT,Row INT,PhoneType VARCHAR(20),

DefaultFlg INT,ContactPerson VARCHAR(20),ContactNumber VARCHAR(20))

INSERT INTO @phoneDetail(CustomerID,Row,PhoneType,DefaultFlg,ContactPerson,ContactNumber)

SELECT 1, 1, 'CompanyPhone', 1, 'Richard', '1876-926-04545' UNION ALL

SELECT 1, 2, 'Home Phone', 0, 'Richard', '1876 946 04789' UNION ALL

SELECT 1, 3, 'Mobile', 0, 'Richard', '495 6764/882222' UNION ALL

SELECT 1, 4, 'Fax', 0, 'Richard', '1876-929-1885' UNION ALL

SELECT 1, 5, 'Fax', 0, 'Richard', '1876-882-555' UNION ALL

SELECT 1, 6, 'Fax', 0, 'Richard', '1876-960 666';

WITH CTE AS (

SELECT CustomerID,Row,PhoneType,DefaultFlg,ContactPerson,ContactNumber,

ROW_NUMBER() OVER(PARTITION BY CustomerID,ContactPerson,PhoneType ORDER BY Row) AS rn

FROM @phoneDetail)

SELECT CustomerID,

ContactPerson,

MAX(CASE WHEN PhoneType='CompanyPhone' THEN ContactNumber END) AS CompanyPhone,

MAX(CASE WHEN PhoneType='Home Phone' THEN ContactNumber END) AS Home,

MAX(CASE WHEN PhoneType='Mobile' THEN ContactNumber END) AS Mobile,

MAX(CASE WHEN PhoneType='Fax' THEN ContactNumber END) AS Fax,

MAX(DefaultFlg) AS DefaultFlg

FROM CTE

GROUP BY CustomerID,ContactPerson,rn;

____________________________________________________

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

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

I personally never use PIVOT because I don't like it the way they wrote it (PIVOT in Access is much easier and more more powerful) and it's comparatively slow.

The following article will show you how both PIVOT and Cross Tabs work so you'll understand what Mark wrote.

http://www.sqlservercentral.com/articles/T-SQL/63681/

--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)

naseer78_pk Mr or Mrs. 500 Points: 592 More actions · Answer 5

It solve my problem

Thanks for helping me.

Regards,

NA