Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


comma separate value show as table


comma separate value show as table

Author
Message
vishnu 9013
vishnu 9013
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 321
I have One table tbaleOne

Column1
-------------------------
AA,BB,CC
DD,EE,FF
GG,HH,II

I need out put

col1 col2 col2
---------------------------------------------------------
AA BB CC
DD EE FF
GG HH II
Suresh B.
Suresh B.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 5326
Is the number of columns fixed/known like 3 as shown in the above example?
BrainDonor
BrainDonor
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2020 Visits: 11184
The easiest way is probably to use the CSV Splitter function, by Jeff Moden (with some assistance from others) - http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Steve Hall
Linkedin
Blog Site
Dave Brooking
Dave Brooking
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 1681
This will do what the OP requested, but highly unlikely it matches the real requirement, poor specification = poor solutionCrying.

select SUBSTRING(Column1,1,2) as col1
,SUBSTRING(Column1,4,2) as col2
,SUBSTRING(Column1,7,2) as col2
from tbaleOne

I expect the splitter solution as suggested by BrainDonor will turn out to be the desired solution.
R.P.Rozema
R.P.Rozema
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 1681
BrainDonor (8/16/2012)
The easiest way is probably to use the CSV Splitter function, by Jeff Moden (with some assistance from others) - http://www.sqlservercentral.com/articles/Tally+Table/72993/.

The splitter function will return 3 rows per input row, not 3 columns as was requested. If you apply the splitter function, you will still have to unpivot the resulting rows into columns. And this is where Suresh B.'s question comes in: how many values are there in the input? If there are always 3 values, then it may be easier to do this with charindex() and substring(). If the number of values can vary you should have a look at the link for cross tab in my signature: using a cross tab you can turn the rows into columns again. But also when it is always 3 values you should still read it.

Here's a little example of how you can turn the rows back into columns using a cross tab:
declare @tbaleOne table (
tbale_id int identity(1,1) not null,
Column1 varchar(100) not null,
primary key(tbale_id)
);

insert @tbaleOne( Column1)
select 'AA,BB,CC'
union all select 'DD,EE,FF'
union all select 'GG,HH,II';

set ansi_warnings off;

select max(case x.pos when 1 then x.val end) as col1,
max(case x.pos when 4 then x.val end) as col2,
max(case x.pos when 7 then x.val end) as col3
from @tbaleOne i
cross apply (
select 1 as pos, substring(i.Column1, 1, 2) as val
union all
select 4 as pos, substring(i.Column1, 4, 2) as val
union all
select 7 as pos, substring(i.Column1, 7, 2) as val
) x
group by i.tbale_id;

set ansi_warnings on;





Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
SomewhereSomehow
SomewhereSomehow
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 469
In particular simple case, when: 1) there not more than 4 columns, 2) column data fits nvarchar(128) (sysname), 3) no dots in data - you may use parsename function like this:
declare @t table(c varchar(100))
insert @t values ('AA,BB,CC'),('DD,EE,FF'),('GG,HH,II')

select
col1 = parsename(replace(c,',','.'),3),
col2 = parsename(replace(c,',','.'),2),
col3 = parsename(replace(c,',','.'),1)
from
@t


But for all cases, for universal stuation, you sould better use csv splitter function mentioned above.


I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru
Twitter: @SomewereSomehow
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5829 Visits: 11401
vishnu 9013 (8/16/2012)
I have One table tbaleOne

Column1
-------------------------
AA,BB,CC
DD,EE,FF
GG,HH,II

I need out put

col1 col2 col2
---------------------------------------------------------
AA BB CC
DD EE FF
GG HH II



You need such output where?
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
Here's another way that is a bit faster than using PARSENAME.


SET NOCOUNT ON;

--== SOME SAMPLE DATA ==--
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;

CREATE TABLE #testEnvironment
(c VARCHAR(100))

--1,000,000 Random rows of data
;WITH Tally (n) AS (
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)
INSERT INTO #testEnvironment
SELECT CAST(ABS(CHECKSUM(NEWID()) % 15) AS VARCHAR(50)) + ',' +
CAST(ABS(CHECKSUM(NEWID()) % 15) AS VARCHAR(50)) + ',' +
CAST(ABS(CHECKSUM(NEWID()) % 15) AS VARCHAR(50))
FROM Tally;

--Holder variable to take display time out of the equation
DECLARE @HOLDER1 VARCHAR(100), @HOLDER2 VARCHAR(100), @HOLDER3 VARCHAR(100);

PRINT REPLICATE('=',80);
PRINT 'PARSENAME';
PRINT REPLICATE('=',80);
SET STATISTICS IO, TIME ON;
select
@HOLDER1 = parsename(replace(c,',','.'),3),
@HOLDER2 = parsename(replace(c,',','.'),2),
@HOLDER3 = parsename(replace(c,',','.'),1)
from
#testEnvironment

SET STATISTICS IO, TIME OFF;

PRINT REPLICATE('=',80);
PRINT 'SUBSTRING';
PRINT REPLICATE('=',80);
SET STATISTICS IO, TIME ON;
SELECT @HOLDER1=SUBSTRING(c, 1, n1 - 1)
,@HOLDER2=SUBSTRING(c, n1 + 1, LEN(c) - (n2 + 1))
,@HOLDER3=SUBSTRING(c, n2 + 2, LEN(c))
FROM #testEnvironment
CROSS APPLY (SELECT CHARINDEX(',', c), LEN(c) - CHARINDEX(',', REVERSE(c))) a(n1, n2)
SET STATISTICS IO, TIME OFF;

DROP TABLE #testEnvironment




Timing results:


================================================================================
PARSENAME
================================================================================
Table '#testEnvironment____________________________________________________________________________________________________0000000000AD'. Scan count 1, logical reads 2351, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 2481 ms, elapsed time = 2467 ms.
================================================================================
SUBSTRING
================================================================================
Table '#testEnvironment____________________________________________________________________________________________________0000000000AD'. Scan count 1, logical reads 2351, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1653 ms, elapsed time = 1673 ms.




My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
sandeep rawat
sandeep rawat
Right there with Babe
Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)

Group: General Forum Members
Points: 725 Visits: 376
Hi if there case one. when coma is exactly after two character .

declare @t table(c varchar(100))
insert @t values ('AA,BB,CC'),('DD,EE,FF'),('GG,HH,II')

select
col1 = SUBSTRING(c,1,2),
col2 = SUBSTRING(c,4,2),
col3 = SUBSTRING(c,7,2)
from
@t

-------2
if there is no rule when coma will be there then write 3 split function which return

first return first part when string is passed ie -if we passed 'aaa,bb,cc' --- will return - 'aaa'
second return second part when string is passed ie -if we passed 'aaa,bb,cc' --- will return - 'bb'
third return third part when string is passed ie -if we passed 'aaa,bb,cc' --- will return - 'cc'


---
SomewhereSomehow
SomewhereSomehow
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 469
dwain.c (8/16/2012)
Here's another way that is a bit faster than using PARSENAME.

If we speak for performance aspect, I think it is not parse name, rather it is replace impact, because of its design.
I rerun your test specifying collation explicitly like that
select
@HOLDER1 = parsename(replace(c COLLATE LATIN1_GENERAL_BIN,',','.'),3),
@HOLDER2 = parsename(replace(c COLLATE LATIN1_GENERAL_BIN,',','.'),2),
@HOLDER3 = parsename(replace(c COLLATE LATIN1_GENERAL_BIN,',','.'),1)
from
#testEnvironment


and got those numbers

================================================================================
PARSENAME
================================================================================
Table '#testEnvironment____________________________________________________________________________________________________000000000043'. Scan count 1, logical reads 2351, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1875 ms, elapsed time = 1870 ms.
================================================================================
SUBSTRING
================================================================================
Table '#testEnvironment____________________________________________________________________________________________________000000000043'. Scan count 1, logical reads 2351, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 2203 ms, elapsed time = 2199 ms.

I also noticed an interesting thing if you specify collation explicitly for substring example - it has no effect. Parsename wins.
But, if you create a table like:
CREATE TABLE #testEnvironment (c VARCHAR(100) COLLATE LATIN1_GENERAL_BIN)
than substring wins again, with this numbers in my tests: SUBSTRING-1334 ms VS PARSENAME 1676 ms.
So, I think, talking about performance we may conclude, that as usual, it depends =)


I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru
Twitter: @SomewereSomehow
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search