October 19, 2012 at 1:53 pm
Hi all,
I have a table with Three coloumns. Path,ID,Filename
DECLARE @a TABLE( Path VARCHAR(MAX), ID Int, Filename Varchar(50))
INSERT @a
SELECT 'ABC\34\645\123\' Path, '10034' ID, 'FirstFile.doc' Filename UNION ALL
SELECT 'A3323C\334324\62334\12w\', ' 12494', 'SecondFile.Doc'
SELECT * FROM @a
I want to run a select query so that the resultant is as follows.
DECLARE @b-2 TABLE( Fold1 VARCHAR(MAX),Fold2 VARCHAR(MAX),Fold3 VARCHAR(MAX),Fold4 VARCHAR(MAX), ID Int, Filename Varchar(50))
INSERT @b-2
SELECT 'ABC' Fold1,'34' Fold2,'645' Fold3 ,'123' Fold4, '10034' ID, 'FirstFile.doc' Filename UNION ALL
SELECT 'A3323C','334324','62334','12w', ' 12494', 'SecondFile.Doc'
SELECT * FROM @b-2
Thanks and Regards
Ravi T
October 19, 2012 at 2:17 pm
Awesome job posting sample data and desired output in a readily consumable format. That makes this a LOT easier.
There are two techniques involved here. The first is to split the string. This can be done by using the DelimitedSplit8k function. Read the article in my signature about splitting strings. It will provide the code for this function.
Then once it is split we need to pivot (or cross tab) the data into new columns. You find two articles about that in my signature. I used the style where the number of columns is consistent and known. Read both of the those articles. If you need the dynamic version it is explained in detail in the second link.
Just make sure you read and understand what is happening here. You are the one who will need to tweak this to your exact implementation and the one who has to support it.
DECLARE @a TABLE( Path VARCHAR(MAX), ID Int, Filename Varchar(50))
INSERT @a
SELECT 'ABC\34\645\123\' Path, '10034' ID, 'FirstFile.doc' Filename UNION ALL
SELECT 'A3323C\334324\62334\12w\', ' 12494', 'SecondFile.Doc'
;with cte as
(
SELECT *
FROM @a
cross apply dbo.delimitedSplit8k(Path, '\')x
)
select
MAX(Case when ItemNumber = 1 then Item else '' end) as Fold1,
MAX(Case when ItemNumber = 2 then Item else '' end) as Fold2,
MAX(Case when ItemNumber = 3 then Item else '' end) as Fold3,
MAX(Case when ItemNumber = 4 then Item else '' end) as Fold4,
ID, FileName
from cte
group by ID, FileName
--DESIRED OUTPUT
DECLARE @b-2 TABLE( Fold1 VARCHAR(MAX),Fold2 VARCHAR(MAX),Fold3 VARCHAR(MAX),Fold4 VARCHAR(MAX), ID Int, Filename Varchar(50))
INSERT @b-2
SELECT 'ABC' Fold1,'34' Fold2,'645' Fold3 ,'123' Fold4, '10034' ID, 'FirstFile.doc' Filename UNION ALL
SELECT 'A3323C','334324','62334','12w', ' 12494', 'SecondFile.Doc'
SELECT * FROM @b-2
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 19, 2012 at 2:33 pm
Hi Sean,
Thanks for the response.
Number of resulting coloumns will be consistant.
From the query you posted ,i could not get the result
------------------------------------------
Msg 208, Level 16, State 1, Line 7
Invalid object name 'dbo.delimitedSplit8k'.
---------------------------------------------
Am i Missing Something?
Newbie here
Regards
Ravi T
October 19, 2012 at 2:35 pm
santa326 (10/19/2012)
Hi Sean,Thanks for the response.
Number of resulting coloumns will be consistant.
From the query you posted ,i could not get the result
------------------------------------------
Msg 208, Level 16, State 1, Line 7
Invalid object name 'dbo.delimitedSplit8k'.
---------------------------------------------
Am i Missing Something?
Newbie here
Regards
Ravi T
Yes you missed the part where I said you should read the article in my signature about splitting strings. In that article you will find the code for that function. PLEASE actually read the article and understand what is going on.
Given that the number of columns is consistent that code should work just fine once you read that article and add that function to your own system. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply