CODEWITHSUNDEEP
sql-server
Chase R Lewis
Chase R Lewis

Reputation: 2307

Transact SQL Pivot a Split String into appropriate columns

My company is using a generic logging database among many products. To prevent the need for a lot of cross database queries some info is stored in delimited fields within the generic data columns for the logging.

I'm wanting to write query's on the data, but I'm unsure how to use Pivot/Unpivot to get the data into appropriate columns?

Below is a generic example using static data for what I'm wanting to do, but not sure how to do it. We unfortunately don't have the built in split string function in SqlServer 2016 so dbo.fnSplitString is my written equivalent which works fine.

 DECLARE @Columns TABLE (
     CustomerNumber VARCHAR(MAX) NOT NULL, 
     FirstName VARCHAR(MAX) NOT NULL, 
     LastName VARCHAR(MAX) NOT NULL);

/* This isn't valid SQL ... unsure how to get this to work */
INSERT INTO @Columns PIVOT SELECT * FROM dbo.fnSplitString('STUFF1,STUFF2,STUFF3',',');

SELECT * FROM @Columns;

Edit:

Using the examples here and https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx I was able to come up with a solution. The split string function also needs to output a position. This was inspired by one of the solutions below just 'OrdinalPosition' needed to be added to the function. The resulting query works.

DECLARE @Columns TABLE (CustomerNumber VARCHAR(MAX) NOT NULL, FirstName VARCHAR(MAX) NOT NULL, LastName VARCHAR(MAX) NOT NULL);

INSERT INTO @Columns select [0], [1], [2] from (SELECT position, splitdata FROM  dbo.fnSplitString('STUFF1,STUFF4,STUFF3',',')) split pivot (MAX(splitdata) FOR position in ([0],[1],[2])) piv;

SELECT * FROM @Columns;

Upvotes: 0

Views: 2221

Answers (2)

Rex
Rex

Reputation: 521

Is this what you are looking for:
I just used "item" as column name. Replace it with the name corresponding to your name that is returned from fnSplitString function.

DECLARE @Columns TABLE (CustomerNumber VARCHAR(MAX) NOT NULL, FirstName 
VARCHAR(MAX) NOT NULL, LastName VARCHAR(MAX) NOT NULL);

 INSERT INTO @Columns
select STUFF1,STUFF2,STUFF3 from (SELECT  item FROM 
dbo.fnSplitString('STUFF1,STUFF2,STUFF3',',')) d
pivot ( max(item)  for item in (STUFF1,STUFF2,STUFF3) ) piv;

SELECT * FROM @Columns;

Upvotes: 1

domenicr
domenicr

Reputation: 352

If your function fnSplitString returns a table then simply 

INSERT INTO @Columns 
SELECT * FROM dbo.fnSplitString('STUFF1,STUFF2,STUFF3',',')
PIVOT
(
MAX(ParsedValue)
FOR OrdinalPosition in ([0], [1], [2], [3])
) x

Upvotes: 0

Related Questions