Transform rows in columns SQL

Question

I created a Query SQL Server that dismembers my Folders and transforms in rows (delimited by "\")

For example: My folder is "N:\TESTEA\subTESTEB" originally. In my new table, this value is: an folder name per row.

N:

TESTEA

subTESTEB

But, I need transform it's rows in columns. Row 1 in Column 1; Row 2 in Column 2; and so on.

Who know any solution for this? One problem: this folders have multi levels, and not a standard level!

I'm clear?

Below is my SQL Query (I don't know format this SQL code here!):

DECLARE @ARRAY VARCHAR(8000), @DELIMITADOR VARCHAR(100), @S VARCHAR(8000),@ARRAYFinal VARCHAR(8000)
select sub_folder_path into #sub_folder_path from GROUP_FOLDER_ACCESS
SELECT @DELIMITADOR = '\'
CREATE TABLE ARRAY(ITEM_ARRAY VARCHAR(8000)) 

WHILE (select COUNT(*) from #sub_folder_path) > 0
BEGIN
SELECT @ARRAY = sub_folder_path from #sub_folder_path ORDER BY sub_folder_path
SET @ARRAYFinal = @ARRAY
SET @ARRAY = @ARRAY + @DELIMITADOR 
WHILE LEN(@ARRAY) > 0
BEGIN
   SELECT @S = LTRIM(SUBSTRING(@ARRAY, 1, CHARINDEX(@DELIMITADOR, @ARRAY) - 1))
   INSERT INTO ARRAY (ITEM_ARRAY) VALUES (@S)
   SELECT @ARRAY = SUBSTRING(@ARRAY, CHARINDEX(@DELIMITADOR, @ARRAY) + 1, LEN(@ARRAY))
END
DELETE #sub_folder_path WHERE sub_folder_path = @ARRAYFinal
END

Adriano Repetti · Accepted Answer · 2014-09-09 09:06:37Z

If you are using SQL 2005 or above, you can use the PIVOT keyword but it's somehow complicate, have a look at this solution too. I just tried it and it works, although I have to admit that I don't completely understand everything.

Assuming you have a table called "Paths" with a column "Path" where your path strings are in:

; with cte1 as (

   select 
     t2.Path,
     t3.split,
     ROW_NUMBER() over (partition by t2.Path order by t2.Path) as num
   from
     (
     select *,
     CAST('<X>'+replace(t.Path,'\','</X><X>')+'</X>' as XML) as xmlfilter
     from Paths t
     ) t2
   cross apply
   (
     select col1data.D.value('.','varchar(50)') as split
     from t2.xmlfilter.nodes('X') as col1data(D)) t3
     )


    select Path, [1], [2], [3], [4], [5], [6], [7]
from 
(
    select Path, num, split
    from CTE1
) as sourcetable
pivot
(max(split) for num IN ([1], [2], [3], [4], [5], [6], [7])) as pivottable

I admit that this is not a fully dynamic version. If you are looking for such a solution you probably have to craete some UDFs for splitting and then use cursors and while loops to fill your data in a previously prepared table.

AB Vyas · Accepted Answer · 2013-08-23 03:49:24Z

0

I have one option for this. you also use pivot for dynamic.

Here is the example please go throw it.

Dyanamic Pivot

also look in this

Five methods converting rows to columns

May be this will help you to achieve you task.

answered Aug 23, 2013 at 3:49

AB Vyas

2,3896 gold badges26 silver badges43 bronze badges

Collectives™ on Stack Overflow

Transform rows in columns SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related