Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

comma separate value show as table Expand / Collapse
Author
Message
Posted Thursday, August 16, 2012 12:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:54 AM
Points: 251, Visits: 173
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




Post #1345683
Posted Thursday, August 16, 2012 12:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:19 AM
Points: 1,101, Visits: 5,271
Is the number of columns fixed/known like 3 as shown in the above example?


Post #1345687
Posted Thursday, August 16, 2012 1:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:48 AM
Points: 1,533, Visits: 7,916
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/.

BrainDonor
Linkedin
Blog Site
Post #1345696
Posted Thursday, August 16, 2012 1:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 7:24 AM
Points: 467, Visits: 1,681
This will do what the OP requested, but highly unlikely it matches the real requirement, poor specification = poor solution.

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.

Post #1345713
Posted Thursday, August 16, 2012 2:00 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:59 AM
Points: 411, Visits: 1,394
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?
Post #1345721
Posted Thursday, August 16, 2012 2:56 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:03 AM
Points: 75, Visits: 437
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
Post #1345757
Posted Thursday, August 16, 2012 3:35 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
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?
Post #1345778
Posted Thursday, August 16, 2012 7:17 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
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!
Post #1346311
Posted Thursday, August 16, 2012 8:09 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 5:10 AM
Points: 713, Visits: 338
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'


---
Post #1346313
Posted Friday, August 17, 2012 12:20 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:03 AM
Points: 75, Visits: 437
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
Post #1346345
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse