Search code examples
sqlsql-serversql-server-2012

Split string and Pivot Result - SQL Server 2012

I am using SQL Server 2012 and I have a table called XMLData that looks like this:

Tag Attribute
tag1 Cantidad=222¬ClaveProdServ=1
tag1 Cantidad=333¬ClaveProdServ=2

The column Tag has many repeated values, what is different is the column Attribute that has a string of attributes separated by "¬". I want to separate the list of attributes and then pivot the table so the tags are the column names.

The result I want is like this:

tag1 tag1
Cantidad=222 Cantidad=333
ClaveProdServ=1 ClaveProdServ=2

I have a custom made function that splits the string since SQL server 2012 doesn't have a premade function that does this. The function I have receives a string as a parameter and the delimiter like so:

select *
from [dbo].[Split]('lol1,lol2,lol3,lol4',',')

this function will return this:

item
lol1
lol2
lol3

I can't find a way to pass the values of the column Attribute as parameter of this function, something like this:

SELECT *
FROM Split(A.Attribute,'¬'),XMLData A

And then put the values of the column Tag as the the column names for each set of Attributes

Solution

  • My magic crystal ball tells me, that you have - why ever - decided to do it this way and any comments about don't store CSV data are just annoying to you.

    How ever...

    If this is just a syntax issue, try it like this:

    SELECT t.Tag
      ,t.Attribute
      ,splitted.item
FROM YourTable AS t
CROSS APPLY dbo.Split(t.Attribute,'¬') AS splitted