TSQL Table Transformation Fields => Columns

Question

I have the following table layout. Each line value will always be unique. There will never be more than one instance of the same Id, Name, and Line.

Id Name Line
1  A    Z
2  B    Y
3  C    X
3  C    W
4  D    W

I would like to query the data so that the Line field becomes a column. If the value exists, a 1 is applied in the field data, otherwise a 0. e.g.

Id Name Z Y X W
1  A    1 0 0 0
2  B    0 1 0 0
3  C    0 0 1 1
4  D    0 0 0 1

The field names W, X, Y, Z are just examples of field values, so I can't apply an operator to explicitly check, for example, 'X', 'Y', or 'Z'. These could change at any time and are not restricted to a finate set of values. The column names in the result-set should reflect the unique field values as columns.

Any idea how I can accomplish this?

Answer 1

Here's a rather exotic approach (using sample data from the old Northwind database). It's adapted from the version here, which no longer worked due to the deprecation of DBCC RENAMECOLUMN and the addition of PIVOT as a keyword.

set nocount on 
create table Sales ( 
  AccountCode char(5), 
  Category varchar(10), 
  Amount decimal(8,2) 
) 
--Populate table with sample data 
insert into Sales 
select customerID, 'Emp'+CAST(EmployeeID as char), sum(Freight) 
from Northwind.dbo.orders 
group by customerID, EmployeeID 
create unique clustered index Sales_AC_C 
on Sales(AccountCode,Category) 
--Create table to hold data column names and positions 
select A.Category, 
       count(distinct B.Category) AS Position 
into #columns 
from Sales A join Sales B 
on A.Category >= B.Category 
group by A.Category 
create unique clustered index #columns_P on #columns(Position) 
create unique index #columns_C on #columns(Category) 
--Generate first column of Pivot table 
select distinct AccountCode into Pivoted from Sales 
--Find number of data columns to be added to Pivoted table 
declare @datacols int 
select @datacols = max(Position) from #columns 
--Add data columns one by one in the correct order 
declare @i int 
set @i = 0 
while @i < @datacols begin 
  set @i = @i + 1 
--Add next data column to Pivoted table 
  select P.*, isnull(( 
    select Amount 
    from Sales S join #columns C 
    on C.Position = @i 
    and C.Category = S.Category 
    where P.AccountCode = S.AccountCode),0) AS X 
  into PivotedAugmented 
  from Pivoted P 
--Name new data column correctly 
  declare @c sysname 
  select @c = Category 
  from #columns 
  where Position = @i 
  exec sp_rename '[dbo].[PivotedAugmented].[X]', @c, 'COLUMN'
--Replace Pivoted table with new table 
  drop table Pivoted 
  select * into Pivoted from PivotedAugmented 
  drop table PivotedAugmented 
end 
select * from Pivoted 
go 
drop table Pivoted 
drop table #columns 
drop table Sales 

Answer 2

If you're doing this for a SQL Server Reporting Services (SSRS) report, or could possibly switch to using one, then stop now and go throw a Matrix control onto your report. Poof! You're done! Happy as a clam with your data pivoted.

Answer 3

Here is the dynamic version

Test table

create table #test(id int,name char(1),line char(1))

insert #test values(1 , 'A','Z')
insert #test values(2 , 'B','Y')
insert #test values(3 , 'C','X')
insert #test values(4 , 'C','W')
insert #test values(5 , 'D','W')
insert #test values(5 , 'D','W')
insert #test values(5 , 'D','P')

Now run this

declare @names nvarchar(4000)

SELECT @names =''
  SELECT  @names    = @names +   line +', '  
    FROM (SELECT distinct  line from #test) x

SELECT @names = LEFT(@names,(LEN(@names) -1))

exec('
SELECT *
 FROM(
SELECT DISTINCT Id, Name,Line
FROM #test
    ) AS pivTemp
PIVOT
(   COUNT(Line)
    FOR Line IN (' + @names +' )
) AS pivTable ')

Now add one row to the table and run the query above again and you will see the B

insert #test values(5 , 'D','B')

Caution: Of course all the problems with dynamic SQL apply, if you can use sp_executeSQL but since parameters are not use like that in the query there really is no point

Answer 4

It's a standard pivot query.

If 1 represents a boolean indicator - use:

  SELECT t.id,
         t.name,
         MAX(CASE WHEN t.line = 'Z' THEN 1 ELSE 0 END) AS Z,
         MAX(CASE WHEN t.line = 'Y' THEN 1 ELSE 0 END) AS Y,
         MAX(CASE WHEN t.line = 'X' THEN 1 ELSE 0 END) AS X,
         MAX(CASE WHEN t.line = 'W' THEN 1 ELSE 0 END) AS W
    FROM TABLE t
GROUP BY t.id, t.name

If 1 represents the number of records with that value for the group, use:

  SELECT t.id,
         t.name,
         SUM(CASE WHEN t.line = 'Z' THEN 1 ELSE 0 END) AS Z,
         SUM(CASE WHEN t.line = 'Y' THEN 1 ELSE 0 END) AS Y,
         SUM(CASE WHEN t.line = 'X' THEN 1 ELSE 0 END) AS X,
         SUM(CASE WHEN t.line = 'W' THEN 1 ELSE 0 END) AS W
    FROM TABLE t
GROUP BY t.id, t.name

Answer 5

Edited following update in question

SQL Server does not support dynamic pivoting.

To do this you could either use dynamic SQL to generate a query along the following lines.

SELECT 
       Id ,Name, 
       ISNULL(MAX(CASE WHEN Line='Z' THEN 1 END),0) AS Z,
       ISNULL(MAX(CASE WHEN Line='Y' THEN 1 END),0) AS Y,
       ISNULL(MAX(CASE WHEN Line='X' THEN 1 END),0) AS X,
       ISNULL(MAX(CASE WHEN Line='W' THEN 1 END),0) AS W
FROM T
 GROUP BY Id ,Name

Or an alternative which I have read about but not actually tried is to leverage the Access Transform function by setting up an Access database with a linked table pointing at the SQL Server table then query the Access database from SQL Server!

Answer 6

As you are using SQL Server, you could possibly use the PIVOT operator intended for this purpose.

Answer 7

Assuming you have a finite number of values for Line that you could enumerate:

declare @MyTable table (
    Id int,
    Name char(1),
    Line char(1)
)

insert into @MyTable
    (Id, Name, Line)
    select 1,'A','Z'
    union all
    select 2,'B','Y'
    union all
    select 3,'C','X'
    union all
    select 3,'C','W'
    union all
    select 4,'D','W'

SELECT Id, Name, Z, Y, X, W
    FROM (SELECT Id, Name, Line
            FROM @MyTable) up
    PIVOT (count(Line) FOR Line IN (Z, Y, X, W)) AS pvt
    ORDER BY Id