CODEWITHSUNDEEP
sql-serverpivotaggregate
steve_o
steve_o

Reputation: 1243

SQL Pivot table without aggregate

I have a number of text files that are in a format similar to what is shown below. 

ENTRY,1,000000,Widget 4000,1,,,2,,
FIELD,Type,A
FIELD,Component,Widget 4000
FIELD,Vendor,Acme 
ENTRY,2,000000,PRODUCT XYZ,1,,,3,
FIELD,Type,B
FIELD,ItemAssembly,ABCD
FIELD,Component,Product XYZ - 123
FIELD,Description1,Product 
FIELD,Description2,XYZ-123 
FIELD,Description3,Alternate Part #440
FIELD,Vendor,Contoso

They have been imported into a table with VARCHAR(MAX) as the only field. Each ENTRY is a "new" item, and all the subsequent FIELD rows are properties of that item. The data next to the FIELD is the column name of the property. The data to the right of the property is the data I want to display.

The desired output would be:

ENTRY                    Type     Component       Vendor    ItemAssembly Description1
1,000000,Widget 4000       A       Widget 4000      Acme
2,000000,Product XYZ       B       Product XYZ-123  Contoso  ABCD         Product

I've got the column names using the code below (there are several tables that I have UNIONed together to list all the property names).

select @cols = 
STUFF (
(select Distinct ', ' + QUOTENAME(ColName)  from 
(SELECT 
    SUBSTRING(ltrim(textFileData),CHARINDEX(',', textFileData, 1)+1,CHARINDEX(',', textFileData, CHARINDEX(',', textFileData, 1)+1)- CHARINDEX(',', textFileData, 1)-1) as ColName
  FROM [MyDatabase].[dbo].[MyTextFile]
  where 
    (LEFT(textFileData,7) LIKE @c) 

  UNION
  ....
  ) A
  FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'')

Is a Pivot table the best way to do this? No aggregation is needed. Is there a better way to accomplish this? I want to list out data next to the FIELD name in a column format.

Thanks!

Upvotes: 1

Views: 155

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Here your are, this comes back exactly as you need it. I love tricky SQL :-). This is a real ad-hoc singel-statement call.

DECLARE @tbl TABLE(OneCol VARCHAR(MAX));
INSERT INTO @tbl
VALUES('ENTRY,1,000000,Widget 4000,1,,,2,,')
     ,('FIELD,Type,A')
     ,('FIELD,Component,Widget 4000')
     ,('FIELD,Vendor,Acme ')
     ,('ENTRY,2,000000,PRODUCT XYZ,1,,,3,')
     ,('FIELD,Type,B')
     ,('FIELD,ItemAssembly,ABCD')
     ,('FIELD,Component,Product XYZ - 123')
     ,('FIELD,Description1,Product ')
     ,('FIELD,Description2,XYZ-123 ')
     ,('FIELD,Description3,Alternate Part #440')
     ,('FIELD,Vendor,Contoso');

WITH OneColumn AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS inx
          ,CAST('<root><r>' + REPLACE(OneCol,',','</r><r>') + '</r></root>' AS XML) AS Split
    FROM @tbl AS tbl
)
,AsParts AS
(
    SELECT inx
          ,Each.part.value('/root[1]/r[1]','varchar(max)') AS Part1
          ,Each.part.value('/root[1]/r[2]','varchar(max)') AS Part2
          ,Each.part.value('/root[1]/r[3]','varchar(max)') AS Part3
          ,Each.part.value('/root[1]/r[4]','varchar(max)') AS Part4
          ,Each.part.value('/root[1]/r[5]','varchar(max)') AS Part5
    FROM OneColumn
    CROSS APPLY Split.nodes('/root') AS Each(part)
)
,TheEntries AS
(
    SELECT DISTINCT *
    FROM AsParts 
    WHERE Part1='ENTRY'
)

SELECT TheEntries.Part2 + ',' + TheEntries.Part3 + ',' + TheEntries.Part4 AS [ENTRY]
        ,MyFields.AsXML.value('(fields[1]/field[Part2="Type"])[1]/Part3[1]','varchar(max)') AS [Type]
        ,MyFields.AsXML.value('(fields[1]/field[Part2="Component"])[1]/Part3[1]','varchar(max)') AS Component
        ,MyFields.AsXML.value('(fields[1]/field[Part2="Vendor"])[1]/Part3[1]','varchar(max)') AS Vendor
        ,MyFields.AsXML.value('(fields[1]/field[Part2="ItemAssembly"])[1]/Part3[1]','varchar(max)') AS ItemAssembly
        ,MyFields.AsXML.value('(fields[1]/field[Part2="Description1"])[1]/Part3[1]','varchar(max)') AS Description1

FROM TheEntries
CROSS APPLY 
(

    SELECT *
    FROM AsParts AS ap
    WHERE ap.Part1='FIELD' AND ap.inx>TheEntries.inx
            AND ap.inx < ISNULL((SELECT TOP 1 nextEntry.inx FROM TheEntries AS nextEntry WHERE nextEntry.inx>TheEntries.inx ORDER BY nextEntry.inx DESC),10000000)
    ORDER BY ap.inx
    FOR XML PATH('field'), ROOT('fields'),TYPE
) AS MyFields(AsXML)

Upvotes: 0

sarh
sarh

Reputation: 6627

Here is the solution in SQL fiddle: http://sqlfiddle.com/#!3/8f0b0/8

Prepare raw data in format (entry, field, value), use dynamic SQL to make pivot on unknown column count.

MAX() for string is enough to simulate "without aggregate" behavior in this case.

create table t(data varchar(max))
insert into t values('ENTRY,1,000000,Widget 4000,1,,,2,,')
insert into t values('FIELD,Type,A')
insert into t values('FIELD,Component,Widget 4000')
insert into t values('FIELD,Vendor,Acme ')
insert into t values('ENTRY,2,000000,PRODUCT XYZ,1,,,3,')
insert into t values('FIELD,Type,B')
insert into t values('FIELD,ItemAssembly,ABCD')
insert into t values('FIELD,Component,Product XYZ - 123')
insert into t values('FIELD,Description1,Product ')
insert into t values('FIELD,Description2,XYZ-123 ')
insert into t values('FIELD,Description3,Alternate Part #440')
insert into t values('FIELD,Vendor,Contoso');

create type preparedtype as table (entry varchar(max), field varchar(max), value varchar(max))


declare @prepared preparedtype

;with identified as
(
  select
    row_number ( ) over (order by (select 1)) as id,
    substring(data, 1, charindex(',', data) - 1) as type,
    substring(data, charindex(',', data) + 1, len(data)) as data  
  from t
)
, tree as 
(
  select
    id,
    (select max(id) 
    from identified
    where type = 'ENTRY'
    and id <= i.id) as parentid,
    type,
    data
  from identified as i
)
, pivotsrc as
(
  select 
    p.data as entry,
    substring(c.data, 1, charindex(',', c.data) - 1) as field,
    substring(c.data, charindex(',', c.data) + 1, len(c.data)) as value
  from tree as p
  inner join tree as c on c.parentid = p.id 
  where p.id = p.parentid
  and c.parentid <> c.id
)
insert into @prepared
select * from pivotsrc

declare @dynamicPivotQuery as nvarchar(max)
declare @columnName as nvarchar(max)

select @columnName = ISNULL(@ColumnName + ',','') 
       + QUOTENAME(field)
from (select distinct field from @prepared) AS fields

set @dynamicPivotQuery = N'select * from @prepared
pivot (max(value) for field in (' + @columnName + ')) as result'

exec sp_executesql @DynamicPivotQuery, N'@prepared preparedtype readonly', @prepared

Upvotes: 1

Related Questions