June 28, 2013 at 3:22 pm
Hey All,
I am using the Moden splitter (thanks!) to split a one column table containing a string into its components.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
I then want to insert the split data in a a destination table. I am using the PIVOT function but because of the need to use an aggregate I am only getting one record.
Can I use the PIVOT function here?
CREATE TABLE #Test
(
ItemNumber int,
Item varchar (3)
)
--Insert data that simulates the output of the string splitter
INSERT INTO #Test (ItemNumber,Item) VALUES (1,'A')
INSERT INTO #Test (ItemNumber,Item) VALUES (1,'B')
INSERT INTO #Test (ItemNumber,Item) VALUES (1,'C')
INSERT INTO #Test (ItemNumber,Item) VALUES (2,'A')
INSERT INTO #Test (ItemNumber,Item) VALUES (2,'D')
INSERT INTO #Test (ItemNumber,Item) VALUES (2,'E')
INSERT INTO #Test (ItemNumber,Item) VALUES (3,'F')
INSERT INTO #Test (ItemNumber,Item) VALUES (3,'Y')
INSERT INTO #Test (ItemNumber,Item) VALUES (3,'Z')
SELECT * FROM #Test
SELECT [1],[2],[3]
FROM
( --the subquery represents the output of the string splitter
SELECT
ItemNumber,
Item
FROM #Test
) P
PIVOT
(MAX(Item)
FOR ItemNumber IN ([1],[2],[3])) AS pvt
DROP TABLE #Test
My desired output is:
column1,column2,column3
A,B,C
A,D,E
F,YZ
not a single record C,E,Z
What do do you folks do with the output of the splitter function to get it into a table?
June 28, 2013 at 3:42 pm
If I understand correctly, your original Strings were
A,A,F
B,D,Y
C,E,Z
Otherwise, I'm not sure that you're getting those results.
However, given the information you supplied, this code would do the trick. It might help if you gave us the entire process to avoid multiple steps if it can be done in a single one.
WITH CTE AS(
SELECT ItemNumber,
Item,
ROW_NUMBER() OVER(PARTITION BY ItemNumber ORDER BY Item) rn
FROM #Test
)
SELECT MAX(CASE WHEN rn = 1 THEN Item END),
MAX(CASE WHEN rn = 2 THEN Item END),
MAX(CASE WHEN rn = 3 THEN Item END)
FROM CTE
GROUP BY ItemNumber
June 28, 2013 at 4:43 pm
Actually works as expected when I actually use the function instead of simulating its output....
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
CREATE TABLE #Source
(
SomeID int,
SomeValue varchar(25)
)
INSERT INTO #Source (SomeID,SomeValue) VALUES (1,'A|B|C')
INSERT INTO #Source (SomeID,SomeValue) VALUES (2,'A|D|E')
INSERT INTO #Source (SomeID,SomeValue) VALUES (3,'F|Y|Z')
--SELECT * FROM #Source
SELECT
S.SomeID,
split.ItemNumber,
Item = QUOTENAME(split.Item,'"')
FROM #Source S
CROSS APPLY dbo.DelimitedSplit8k(S.SomeValue,'|') split
SELECT
SomeID,
[1],
[2],
[3]
FROM
( --the subquery represents the output of the string splitter
SELECT
S.SomeID,
split.ItemNumber,
Item = QUOTENAME(split.Item,'"')
FROM #Source S
CROSS APPLY dbo.DelimitedSplit8k(S.SomeValue,'|') split
) P
PIVOT
(MAX(Item)
FOR ItemNumber IN ([1],[2],[3])) AS pvt
DROP TABLE #Source
June 28, 2013 at 4:48 pm
That's because you have the SomeID column that will help you with the group by. That's why I needed a Row_Number() to simulate the SomeID column.
June 28, 2013 at 4:56 pm
Understood, thank you. I really appreciate the time you spend helping people here.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply