SQL Server Row to Cols

Question

I seems an simple task but I need some help from the sql experts with this one.

Table samples and requested result

The number of contacts can vary from 1 to 30 persons and I want all the phone numbers in one row per cust_ref.

How can I do this??

you can check the http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server link — Jigar Pandya, Jul 14 '15 at 07:59
@Shnugo Here you need also Dynamic SQL, if you want to have dynamic count of columns in your result (based on largest distinct count of cust_ref) ;). — shA.t, Jul 14 '15 at 09:07
@shA.t, well that's true :) if you assume a person having hundreds of phone numbers. I'd pick pivot into so many, that it is surely enough. The biggest advantage: You can put this into an UDF and bind it everywhere. Btw: "can vary from 1 to 30" seems to set this as max range... — Shnugo, Jul 14 '15 at 09:12
Thanks for you answers but the problem is I have to do it in a sql query. I cannot use UDF. — Aren, Jul 14 '15 at 10:15
@Aren please find out below answer is pure T-sql Script used pivot and Cross apply to match your requirement — mohan111, Jul 14 '15 at 11:02

score 1 · Accepted Answer · answered Jul 14 '15 at 08:40

1

This is a solution without dynamic sql, please try out:

declare @contacts table(cust_ref int,phone varchar(100));
insert into @contacts values(10000,'ph10000a'),(10000,'ph10000b'),(10000,'ph10000c')
                           ,(10001,'ph10001a'),(10001,'ph10001b'),(10001,'ph10001c') 
                           ,(10002,'ph10002a'),(10002,'ph10002b');

SELECT p.*
FROM
( 
    SELECT 'phone'+REPLACE(STR(ROW_NUMBER() OVER(PARTITION BY cust_ref ORDER BY phone),2),' ','0') AS inx,*
    FROM @contacts    
) AS tbl
PIVOT
(
    MIN(phone) FOR inx IN(phone01,phone02,phone03,phone04) --type more...
) AS p

answered Jul 14 '15 at 08:40

Shnugo

66,100
9
53
114

Thank you Shnugo but I have to do it with an sql query. I cannot use functions. Is there a solution without UDF? – Aren Jul 14 '15 at 10:17
Sorry, don't understand this... The posted solution is pure SQL, no UDF. Just adapt this to your needs / table names... And please tick as accepted and/or up vote if helpful... – Shnugo Jul 14 '15 at 10:28
Your right after adapting the @contact for the real database content it works GREAT!! Thank you Shnugo! – Aren Jul 14 '15 at 10:39
Glad to help you, thx for the reputation points... – Shnugo Jul 14 '15 at 10:43
@Aren you have posted 2 tables and asked for requirement but shnugo done all in one table so how it will give required output – mohan111 Jul 14 '15 at 11:01
@mohan111, the only strucutural difference between yours and mine is the UNION. I suppose Aren is clever having found this... Btw: using BigInt will cut the leading zero in the output. – Shnugo Jul 14 '15 at 11:10
@Shnugo what ever the data have been given you have modified that and changed the data according to your requirement like 'ph10002a' where this come from i didn't understand ?? i haven't hardcoded anything in my script it's not about the clever enough to find out it is all about giving the proper answer.So that in future the questioner will have better idea when this kind of requirement comes – mohan111 Jul 14 '15 at 11:37
@Shnugo as you have identifed the bigInt will cut leading zeros so i have converted datatype into varchar to keep leading zeros still the result set will be the same.So now clarify my question why you have added ph10002a and only single table instead of oth tables – mohan111 Jul 14 '15 at 11:43
@mohan111, well: test data should allow immediate result check. Therefor I prefer test data which is "readable" (in this case in point of view of reference and sorting). Btw: "readable"... More important than this is the readability of the exampel. Do you think someone with little SQL knowledge can understand CROSS APPLY (VALUES ('Phone',Phone))CS(Col,val))T ? Let's stop this... The OP is happy so should be we... – Shnugo Jul 14 '15 at 12:32
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/83215/discussion-between-mohan111-and-shnugo). – mohan111 Jul 14 '15 at 13:10

mohan111 · Answer 2 · 2015-07-14T11:41:22.673

by using CROSS APPLY,CTE and UNION ALL we can get the above requirement

declare @tableA table (RefId int,Phone varchar(10))
insert into @tableA (RefId,Phone)values (10000,'030123456'),(100123,060123445)

Declare @tableB table (CustID  INT,RefId INT,Phone varchar(10)))
insert into @tableB (CustID,RefId,Phone)values 
(1,10000,'030245789'),
(2,10000,'030245889'),
(1,100123,'060245789'),
(2,100123,'060243389'),
(3,100123,'060289389')

; with CTE AS (
select A.RefId,A.Phone As Phone from @tableA A 
UNION 
select B.RefId,B.Phone As Phone from @tableB B )

 select * from (
 Select RefId,
 val,
 COL + CAST(ROW_NUMBER()OVER(PARTITION BY RefId ORDER BY RefId) AS VARCHAR(1))RN  
    FROM CTE 
CROSS APPLY (VALUES ('Phone',Phone))CS(Col,val))T
PIVOT (MAX(VAL) FOR RN IN ([Phone1],[Phone2],[Phone3],[Phone4]))P

SQL Server Row to Cols

2 Answers2