Converting columns to rows.

Question

Hi,

I have values something like this

SELECT 64291,64316,64529,64554,64612,64653,64671

and i want to convert them to

64291

64316

64529

64554

64612

64653

64671

i.e. i want to convert columns to rows. i dont want to use union as i can have 1000's of values in select list. is there any other way to do it.

Thanks

Answer 1

Hi,

Do you have a single column having all the values or having mutiple columns with each column  having single value?

If you have a single column with all the values  then try this:

declare @aa table
(cola varchar(max))
insert into @aa 
select '64291,64316,64529,64554,64612,64653,64671'

declare @temp varchar(max)
set @temp=(select cola from @aa)
declare @index int
declare @text varchar(max)
declare @Temptable Table (cola varchar(max)) 


while LEN(@temp)>2
BEgin
SET @index = CHARINDEX(',', @temp)

IF (@index = 0) AND (LEN(@temp) > 2)
          BEGIN  
            Insert  into @Temptable(cola)(select @temp)
            BREAK 
        END 



set @text=SUBSTRING (@temp,0,@index)

set @temp=REPLACE(@temp,@text+',','')
insert  into @Temptable(cola)
(select @text)

END


select * from @Temptable

Thanks,

Saikat

Answer 2

Check this blog post to find your solution: http://sqlwithmanoj.wordpress.com/2010/09/16/combine-multiple-rows-to-csv-string-and-vice-versa/

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 3

Try this:

declare @aa table
(cola varchar(max))
insert into @aa 
select '64291,64316,64529,64554,64612,64653,64671'

;WITH cteSplit(CSVVal)
AS (
	SELECT 
		CAST('<r><i>' + REPLACE(cola, ',', '</i><i>') + '</i></r>' AS XML) AS CSVVal
	FROM @aa
)
SELECT
	r.i.value('.', 'VARCHAR(50)') AS [ColA]
FROM    cteSplit AS s
CROSS APPLY CSVVal.nodes('r/i') AS r(i)

---

Krishnakumar S