February 10, 2010 at 12:34 pm
I have this data in 1 column and 8 rows . I want to distribute it to multiple columns , the delimiter is '|'.
A | B | C | 10
A | B | C | 10 | jhv
A | B | C
A | B | C | 10 | jhv | gedfbv
A | B | C | 10
A | B | C | 10 | jhv
A | B | C | 10 | jhv
A | B | C | 10 | jhv
So my result table will have 6 columns and 8 rows with null values in the row/columns where there are no values . How do I achieve this .
Thanks
February 10, 2010 at 1:11 pm
Please search this site for string split functions or have a look at the Tally Table link in my signature which includes an efficient example.
February 10, 2010 at 1:13 pm
Here's one method. The tally table that Lutz mentioned would also be very efficient.
-- first, notice how I create a table and populate it with the test data?
-- This makes is SOOOOO much easier for all of us volunteers to just cut-and-paste into SSMS.
DECLARE @test-2 TABLE (RowData varchar(50))
INSERT INTO @test-2
SELECT 'A | B | C | 10 ' UNION ALL
SELECT 'A | B | C | 10 | jhv ' UNION ALL
SELECT 'A | B | C ' UNION ALL
SELECT 'A | B | C | 10 | jhv | gedfbv ' UNION ALL
SELECT 'A | B | C | 10 ' UNION ALL
SELECT 'A | B | C | 10 | jhv ' UNION ALL
SELECT 'A | B | C | 10 | jhv ' UNION ALL
SELECT 'A | B | C | 10 | jhv'
;WITH CTE1 AS
(-- get the position of the first delimiter
select RowData,
Col1EndPos = CHARINDEX('|', rowData)
from @test-2
),CTE2 AS
(-- get the position of the (optional) second delimiter
select RowData,
Col1EndPos,
Col2EndPos = CASE WHEN Col1EndPos > 0 THEN CHARINDEX('|', RowData, Col1EndPos+1) ELSE 0 END
from CTE1
),CTE3 AS
(-- get the position of the (optional) third delimiter
select RowData,
Col1EndPos,
Col2EndPos,
Col3EndPos = CASE WHEN Col2EndPos > 0 THEN CHARINDEX('|', RowData, Col2EndPos+1) ELSE 0 END
from CTE2
),CTE4 AS
(-- get the position of the (optional) fourth delimiter
select RowData,
Col1EndPos,
Col2EndPos,
Col3EndPos,
Col4EndPos = CASE WHEN Col3EndPos > 0 THEN CHARINDEX('|', RowData, Col3EndPos+1) ELSE 0 END
from CTE3
),CTE5 AS
(-- get the position of the (optional) fifth delimiter
select RowData,
Col1EndPos,
Col2EndPos,
Col3EndPos,
Col4EndPos,
Col5EndPos = CASE WHEN Col4EndPos > 0 THEN CHARINDEX('|', RowData, Col4EndPos+1) ELSE 0 END
from CTE4
), CTE6 AS
(-- get the end of the data
select RowData,
Col1EndPos,
Col2EndPos,
Col3EndPos,
Col4EndPos,
Col5EndPos,
Col6EndPos = CASE WHEN Col5EndPos > 0 THEN Len(RowData) ELSE 0 END
from CTE5
)
select Col1 = LEFT(RowData, Col1EndPos-1),
Col2 = CASE WHEN Col2EndPos > 0 then SUBSTRING(RowData, Col1EndPos+1, Col2EndPos-Col1EndPos-1)
WHEN Col2EndPos = 0 and Col1EndPos > 0 then Substring(RowData, Col1EndPos+1, len(RowData)) END,
Col3 = CASE WHEN Col3EndPos > 0 then SUBSTRING(RowData, Col2EndPos+1, Col3EndPos-Col2EndPos-1)
WHEN Col3EndPos = 0 and Col2EndPos > 0 then Substring(RowData, Col2EndPos+1, len(RowData)) END,
Col4 = CASE WHEN Col4EndPos > 0 then SUBSTRING(RowData, Col3EndPos+1, Col4EndPos-Col3EndPos-1)
WHEN Col4EndPos = 0 and Col3EndPos > 0 then Substring(RowData, Col3EndPos+1, len(RowData)) END,
Col5 = CASE WHEN Col5EndPos > 0 then SUBSTRING(RowData, Col4EndPos+1, Col5EndPos-Col4EndPos-1)
WHEN Col5EndPos = 0 and Col4EndPos > 0 then Substring(RowData, Col4EndPos+1, len(RowData)) END,
Col6 = CASE WHEN Col6EndPos > 0 then SUBSTRING(RowData, Col5EndPos+1, Col6EndPos-Col5EndPos-1)
WHEN Col6EndPos = 0 and Col5EndPos > 0 then Substring(RowData, Col5EndPos+1, len(RowData)) END
from CTE6
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 10, 2010 at 5:45 pm
With CTE .... in sql 2000 gives me a syntax error...any other way of doing this ?
Server: Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'WITH'.
Thanks
February 10, 2010 at 6:10 pm
Yes there is. Does the table with this data in it have a Primary Key?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2010 at 7:52 pm
Jeff Moden (2/10/2010)
Yes there is. Does the table with this data in it have a Primary Key?
Never mind... can't wait. This will work with or without a primary key on the original data. First, we need to setup a test. This is the way that you should post data whenever you need help so you get better answers quicker. Read the comments for an article that shows an easy way to do it with real data...
--===== Do this test in a nice safe place that everyone has
USE TempDB
GO
--===== Before we even think of starting, create a Tally table.
-- See the following URL for how a Tally table can replace some While Loops.
-- http://www.sqlservercentral.com/articles/T-SQL/62867/
--=============================================================================
-- Create and populate a Tally table
-- http://www.sqlservercentral.com/articles/T-SQL/62867/
--=============================================================================
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Let the public use it
GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC
GO
--===== Next, let's setup your data the way you should have submitted it so we can test.
-- Please see the following article before you post in the future. You should also
-- make it a point to post in the correct forum so you don't tick off the people that
-- are trying to help you.
-- http://www.sqlservercentral.com/articles/Best+Practices/61537/
--=============================================================================
-- Create the originally posted data in a table so we can test
--=============================================================================
CREATE TABLE #OriginalData
(DelimitedData VARCHAR(8000))
INSERT INTO #OriginalData
(DelimitedData)
SELECT 'A | B | C | 10 ' UNION ALL
SELECT 'A | B | C | 10 | jhv ' UNION ALL
SELECT 'A | B | C ' UNION ALL
SELECT 'A | B | C | 10 | jhv | gedfbv ' UNION ALL
SELECT 'A | B | C | 10 ' UNION ALL
SELECT 'A | B | C | 10 | jhv ' UNION ALL
SELECT 'A | B | C | 10 | jhv ' UNION ALL
SELECT 'A | B | C | 10 | jhv'
GO
... and here's a high speed solution that uses the Tally table. Again, read the comments.
--=============================================================================
-- Precondition the data so we don't have to deal with "ragged right"
-- delimiters or missing PK's
--=============================================================================
--===== Conditionally drop the working table so we can easily do reruns for testing
IF OBJECT_ID('TempDB..#Work','U') IS NOT NULL
DROP TABLE #Work
--===== Precondition the data to fix the "ragged right" stuff and create/populate
-- the working table on the fly.
SELECT IDENTITY(INT,1,1) AS RowNum,
'|' --Add a leading delimiter
+ DelimitedData
+ REPLICATE('|',6-(LEN(DelimitedData)-LEN(REPLACE(DelimitedData,'|','')))) --Add missing/trailing delimiters
AS ConditionedData
INTO #Work
FROM #OriginalData
--=============================================================================
-- Split the data and reassemble it in columns.
--=============================================================================
SELECT --===== Classic Cross-Tab to reassemble the split data
MAX(CASE WHEN split.Element = 1 THEN split.ElementValue END) AS Col1,
MAX(CASE WHEN split.Element = 2 THEN split.ElementValue END) AS Col2,
MAX(CASE WHEN split.Element = 3 THEN split.ElementValue END) AS Col3,
MAX(CASE WHEN split.Element = 4 THEN split.ElementValue END) AS Col4,
MAX(CASE WHEN split.Element = 5 THEN split.ElementValue END) AS Col5,
MAX(CASE WHEN split.Element = 6 THEN split.ElementValue END) AS Col6
FROM ( --=== Split the data with row and element numbers
SELECT TOP 2147483647
RowNum,
Element = t.N-DATALENGTH(REPLACE(LEFT(w.ConditionedData,t.N), '|', '')),
ElementValue = LTRIM(RTRIM(SUBSTRING(w.ConditionedData, t.N+1, CHARINDEX('|', w.ConditionedData, t.N+1)-t.N-1)))
FROM #Work w
CROSS JOIN dbo.Tally t
WHERE SUBSTRING(w.ConditionedData, t.N, 1) = '|'
AND t.N < LEN(w.ConditionedData)
ORDER BY w.RowNum, t.N
)split
GROUP BY split.RowNum
ORDER BY split.RowNum
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2010 at 8:45 pm
sqlserver12345 (2/10/2010)
With CTE .... in sql 2000 gives me a syntax error...any other way of doing this ?Server: Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'WITH'.
Thanks
Note that you did post this request in a SQL 2008 forum, so you got a SQL 2008 response. If you needed it for 2000, you should have posted it there.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 10, 2010 at 10:47 pm
My bad... not sure what I was thinking. Previous code will return blanks and not nulls. The following will return nulls for the missing data and is a bit simpler to boot.
--=============================================================================
-- Precondition the data so we don't have to deal with "ragged right"
-- delimiters or missing PK's
--=============================================================================
--===== Conditionally drop the working table so we can easily do reruns for testing
IF OBJECT_ID('TempDB..#Work','U') IS NOT NULL
DROP TABLE #Work
--===== Precondition the data to fix the "ragged right" stuff and create/populate
-- the working table on the fly.
SELECT IDENTITY(INT,1,1) AS RowNum,
'|' --Add a leading delimiter
+ DelimitedData
+ '|'
AS ConditionedData
INTO #Work
FROM #OriginalData
--=============================================================================
-- Split the data and reassemble it in columns.
--=============================================================================
SELECT --===== Classic Cross-Tab to reassemble the split data
MAX(CASE WHEN split.Element = 1 THEN split.ElementValue END) AS Col1,
MAX(CASE WHEN split.Element = 2 THEN split.ElementValue END) AS Col2,
MAX(CASE WHEN split.Element = 3 THEN split.ElementValue END) AS Col3,
MAX(CASE WHEN split.Element = 4 THEN split.ElementValue END) AS Col4,
MAX(CASE WHEN split.Element = 5 THEN split.ElementValue END) AS Col5,
MAX(CASE WHEN split.Element = 6 THEN split.ElementValue END) AS Col6
FROM ( --=== Split the data with row and element numbers
SELECT TOP 2147483647
RowNum,
Element = t.N-DATALENGTH(REPLACE(LEFT(w.ConditionedData,t.N), '|', '')),
ElementValue = LTRIM(RTRIM(SUBSTRING(w.ConditionedData, t.N+1, CHARINDEX('|', w.ConditionedData, t.N+1)-t.N-1)))
FROM #Work w
CROSS JOIN dbo.Tally t
WHERE SUBSTRING(w.ConditionedData, t.N, 1) = '|'
AND t.N < LEN(w.ConditionedData)
ORDER BY w.RowNum, t.N
)split
GROUP BY split.RowNum
ORDER BY split.RowNum
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2010 at 5:50 am
This won't help you in SQL Server 2000 but works nicely in later versions
IF NOT OBJECT_ID('tempdb.dbo.#FOO', 'U') IS NULL
DROP TABLE #FOO
SELECT 'A|B|C|10' AS BulkColumn
INTO #FOO
UNION ALL SELECT 'A|B|C|10|jhv'
UNION ALL SELECT 'A|B|C'
UNION ALL SELECT 'A|B|C|10|jhv|gedfbv'
UNION ALL SELECT 'A|B|C|10'
UNION ALL SELECT 'A|B|C|10|jhv'
UNION ALL SELECT 'A|B|C|10|jhv'
UNION ALL SELECT 'A|B|C|10|jhv'
;
WITH cteTally
AS
(
SELECT TOP 20000 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS pk
FROM master.sys.All_Columns t1
CROSS JOIN master.sys.All_Columns t2
)
SELECT * FROM #FOO
CROSS APPLY
(
SELECT
[1] AS COL1,
[2] AS COL2,
[3] AS COL3,
[4] AS COL4,
[5] AS COL5,
[6] AS COL6
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW,
NULLIF(SUBSTRING(BulkColumn+'|', pk, CHARINDEX('|', BulkColumn+'|', pk)-pk), '') AS Value
FROM cteTally
WHERE pk-1<LEN(BulkColumn)+LEN('|') AND SUBSTRING('|' + BulkColumn + '|', pk, 1)='|'
) AS Z
PIVOT
(
MAX(Value) for ROW in
(
[1],
[2],
[3],
[4],
[5],
[6]
)
)
AS pvt
)
AS Y
February 12, 2010 at 6:30 am
@sqlserver12345,
Any feedback on this? Did any of this work for you?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 12, 2010 at 1:56 pm
Col1 Col2 Col3 Col4 Col5 Col6
ted
This is what I got in the result.
I have 50000 rows in my original table
Thanks
February 12, 2010 at 2:04 pm
appreciate your replies...working on it...will get back to you.
thanks
February 12, 2010 at 3:07 pm
Jeff Moden (2/12/2010)
@sqlserver12345,Any feedback on this? Did any of this work for you?
This is what happens when candidates are left alone in a room with a written test and an internet-enabled phone 😀
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 12, 2010 at 3:45 pm
Thanks Jeff. Works like a charm !
February 13, 2010 at 10:37 am
I haven't tried his, but Steve's looks like it'll work just fine (as he says, not in 2000), as well. He does it by building an on-the-fly Tally table in a CTE and a Pivot instead of a classic Cross-Tab.
Thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply