Solved

Using table data as headers

Posted on 2006-07-15
11
474 Views
Last Modified: 2008-02-26
Hi,

I have a table of data that contains three columns: Key (nvarchar), Title (nvarchar), Value (float). Using SQL, how do I pivot the data into another table (either a temporary table or query) so the values in Title become the headers and the values of Value are grouped by Key and are assigned to the new header. So, in this example:

Key        Title        Value
A             1a           100
A             2a            50
B             1a            70
B             2a            30

Becomes:

Key          1a         2a
A             100       50
B              70        30


Thanks in advance...


0
Comment
Question by:cartti
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17115145
If the title contains only 1a and 2a

SELECT DISTINCT T.Key,
      (SELECT Value FROM urTable WHERE [Key] = t.[Key] and Title = '1a') [1a],
      (SELECT Value FROM urTable WHERE [Key] = t.[Key] and Title = '1a') [2a]
FROM urTable T
0
 
LVL 2

Author Comment

by:cartti
ID: 17115155
Unfortunately, it doesn't. However, your answer merits some credit as that in itself will be useful - so thanks for that. In reality, I won't know in advance what the values will be, other than it should be no more than a dozen.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17115190
Hi cartti,

In that case, you should probably either use a stored procedure, or dynamically build a SQL string and then
use EXEC to run it.

Maybe something like:

DECLARE @SQL varchar(2000)

SET @SQL = 'SELECT DISTINCT T.Key'
SELECT @SQL = @SQL + ', (SELECT Value FROM urTable WHERE [Key] = t.[Key] AND Title = ' + t.Title + ') AS [' + t.Title + ']'
Set @SQL = @SQL + ' FROM urTable t ORDER BY t.Key'
EXEC @SQL

Regards,

Patrick
0
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17115199
cartti,

My SQL might have to be amended to:

DECLARE @SQL varchar(2000)

SET @SQL = 'SELECT DISTINCT T.Key'
SELECT @SQL = @SQL + ', (SELECT Value FROM urTable WHERE [Key] = t.[Key] AND Title = ''' + t.Title + ''') AS [' + t.Title + ']'
Set @SQL = @SQL + ' FROM urTable t ORDER BY t.Key'
EXEC @SQL

Those are all single quotes in there, BTW.

Regards,

Patrick
0
 
LVL 2

Author Comment

by:cartti
ID: 17115216
Thanks Patrick - I'll give that a go when I'm at work on Monday.
0
 
LVL 42

Expert Comment

by:Eugene Z
ID: 17115846
there is 1 of solutions for your question:

create table test2 ([Key]  varchar(2), [Title] varchar(2), [Value] float)
-----------
insert into test2

select 'A',             '1a',           100
union all select 'A' ,            '2a',            50
union all select 'B' ,            '1a',            70
union all select 'B',             '2a',           30
select * from test2
---
Select a.[key],
a.[1a],b.[2a]
from
(
select
[key],
case when title='1a' then value   end '1a',
null '2a'
from test2 ) a

inner join
(
select
[key],
null '1a',
case when title='2a' then  value  end '2a'
from test2 ) b
on
a.[key]=b.[key]
where
a.[1a] is not null
and
b.[2a]  is not null
------

--result:
--Key          1a         2a
--A             100       50
--B              70        30
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17116580
hi cartti ,

If you dont have values other than 1a and 2a then go with the suggestion of aneesh.
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 50 total points
ID: 17116668
If you have sql 2005 then use the PIVOT extension...

(but you have to know in advance what the "titles" will be..)

otherwise you can try this...

http://www.microsoft.com/sql/ce/techinfo/20bol.asp

EXEC sp_TRANSFORM 'SUM','PM_EmpAssignedID','vw_Loc_Pos_Dflt_Rem2','PM_PositionsID','Position'

IF OBJECT_ID('sp_Transform', 'P') IS NOT NULL
     DROP PROCEDURE sp_Transform
GO
CREATE PROCEDURE sp_TRANSFORM
/*
   Purpose:     Creates a Pivot(tm) table for the specified table,
                view or select statement
   Author:      svenh@itrain.de
   Version:     1.1
   History:     march 2000 version 1.0
                july 2002  version 1.1

   Input parameters:
       @Aggregate_Function (optional)
            the aggregate function to use for the pivot
                default function is SUM
        @Aggregate_Column
                name of column for aggregate
        @TableOrView_Name
                name of table or view to use
                if name contains spaces or other special
                characters [] should be used
                Can also be a valid SELECT statement
        @Select_Column
                Column for first column in result table
                for this column row values are displayed
        @Pivot_Column
                Column that is transformed into columns
                for this column column values are displayed
     @DEBUG
          Set this flag to 1 to get debug-information
   
      Example usage:
        Table given   aTable
        content:      Product    Salesman    Sales
                      P1         Sa          12
                      P2         Sb          10
                      P2         Sb          3
                      P3         Sa          12
                      P1         Sc          8
                      P3         Sa          1
                      P2         Sa          NULL
     CALL
     EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
or      EXEC sp_Transform @Aggregate_Column='Sales', @TableOrViewName='aTable',
                            @Select_Column='Product', @Pivot_Column='Salesman'

Result:
        Product| Sa       | Sb       | Sc      | Total      
        -------+----------+----------+---------+---------
        P1     | 12,00    |  0,00    |  8,00   |  20,00
        P2     |  0,00    | 13,00    |  0,00   |  13,00
        P3     | 13,00    |  0,00    |  0,00   |  13,00
        -------+----------+----------+---------+---------
        Total  | 25,00    | 13,00    |  8,00   |  46,00


*/
     @Aggregate_Function nvarchar(30) = 'SUM',
     @Aggregate_Column   nvarchar(255),
     @TableOrView_Name   nvarchar(255),
     @Select_Column         nvarchar(255),
     @Pivot_Column       nvarchar(255),
     @DEBUG              bit = 0
AS
SET NOCOUNT ON
DECLARE @TransformPart   nvarchar(4000)
DECLARE @SQLColRetrieval nvarchar(4000)
DECLARE @SQLSelectIntro  nvarchar(4000)
DECLARE @SQLSelectFinal  nvarchar(4000)

IF @Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG', 'STDEV', 'VAR', 'VARP', 'STDEVP')  
     BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1, @Aggregate_Function) END
ELSE
BEGIN
     SELECT @SQLSelectIntro = 'SELECT CASE WHEN (GROUPING('  +
                         QUOTENAME(@Select_Column)       +
                    ') = 1) THEN ''Total'' ELSE '   +
                    'CAST( + '                      +
                                QUOTENAME(@Select_Column)       +
                    ' AS NVARCHAR(255)) END As '    +
                    QUOTENAME(@Select_Column)       +
                    ', '
     IF @DEBUG = 1 PRINT @sqlselectintro
     SET @SQLColRetrieval =
     N'SELECT @TransformPart = CASE WHEN @TransformPart IS NULL THEN ' +
               N'''' + @Aggregate_Function + N'(CASE CAST(' +
               QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
               N' AS VARCHAR(255)) WHEN '''''' + CAST('  +
               QUOTENAME(@Pivot_Column) +
               N' AS NVarchar(255)) + '''''' THEN ' + @Aggregate_Column +
               N' ELSE 0 END) AS '' + QUOTENAME(' +
               QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
               N') ELSE  @TransformPart + '', ' + @Aggregate_Function +
               N' (CASE CAST(' + QUOTENAME(@Pivot_Column) +
               N' AS nVARCHAR(255)) WHEN '''''' + CAST(' +
               QUOTENAME(CAST(@Pivot_Column As VarChar(255))) +
               N' AS nVARCHAR(255)) + '''''' THEN ' +
               @Aggregate_Column +
               N' ELSE 0 END) AS '' + QUOTENAME(' +
               QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
               N') END FROM (SELECT DISTINCT ' +
               QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +
               N' FROM ' + @TableOrView_Name + ') SelInner'
     IF @DEBUG = 1 PRINT @SQLColRetrieval    
     EXEC sp_executesql @SQLColRetrieval,
                           N'@TransformPart nvarchar(4000) OUTPUT',
                           @TransformPart OUTPUT
     IF @DEBUG = 1 PRINT @TransformPart
     SET @SQLSelectFinal =
                           N', ' + @Aggregate_Function + N'(' +
                           CAST(@Aggregate_Column As Varchar(255)) +
                           N') As Total FROM ' + @TableOrView_Name + N' GROUP BY ' +
                           @Select_Column + N' WITH CUBE'
     IF @DEBUG = 1 PRINT @SQLSelectFinal
     EXEC (@SQLSelectIntro + @TransformPart + @SQLSelectFinal)
END
GO


/*
To test the preceding stored procedure uncomment the
following section

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_NAME = 'aTable')
   DROP TABLE aTable  
--GO
 
CREATE TABLE aTable(Product varchar(2), Salesman varchar(2), Sales int)
INSERT INTO aTable VALUES('P1', 'Sa', 12)
INSERT INTO aTable VALUES('P2', 'Sb', 10)
INSERT INTO aTable VALUES('P2', 'Sb', 3)
INSERT INTO aTable VALUES('P3', 'Sa', 12)
INSERT INTO aTable VALUES('P1', 'Sc', 8)
INSERT INTO aTable VALUES('P3', 'Sa', 1)
INSERT INTO aTable VALUES('P2', 'Sa', NULL)

EXEC sp_Transform @Aggregate_Column='Sales', @TableOrView_Name='aTable',
                            @Select_Column='Product', @Pivot_Column='Salesman'

*/
-- Example from Northwind Table
-- USE NORTHWIND
-- GO
-- EXEC sp_transform 'sum', 'unitprice', 'products', 'productname', 'CategoryID'
-- GO


0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17117476
Lowfatspread,

Interesting sproc; I sall definitely have to bookmark this one!

I would be interested to get your feedback on the SQL I posted earlier.

A relative newcomer to serious T-SQL,

Patrick
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17117522
cartti,

I did a little testing, and my original syntax does not cut it.  This syntax appears to be working though:

DECLARE @SQL varchar(2000)
SELECT DISTINCT RTRIM(aaa.Title) AS Cols
INTO #frame
FROM aaa
SET @SQL = 'SELECT DISTINCT t1.[Key]'
SELECT @SQL = @SQL + ', (SELECT SUM(t2.[Value]) FROM aaa t2 WHERE t2.[Key] = t1.[Key] AND t2.Title = ''' +
      #frame.Cols + ''') AS [' + #frame.Cols + ']'
FROM #frame
Set @SQL = @SQL + ' FROM aaa t1 ORDER BY t1.[Key]'
EXEC (@SQL)
DROP TABLE #frame


Lowfatspread's sproc is a great general solution, though!

Patrick
0
 
LVL 42

Accepted Solution

by:
Eugene Z earned 200 total points
ID: 17117799
There is what you need with ability to have any #columns- titles - it will generate table for you:
--it is working example that will create test table (you can use your own)...

if object_id('test2') is not null
begin
 drop table test2
end
create table test2 ([Key]  varchar(2), [Title] varchar(2), [Value] float)
-----------
insert into test2

select 'A',             '1a',           100
union all select 'A' ,            '2a',            50
union all select 'B' ,            '1a',            70
union all select 'B',             '2a',           30
--union all select 'c',             '3a',           30
--select * from test2
---

declare @count int
declare @sql varchar(1000)

select   identity(int,1,1) cnt,  title into #t  from test2 group by title


set @count =1
set @sql='Select distinct[key]'
while @count<= (select count(distinct title) from test2)
Begin
set @sql=@sql + ','+ (select 'sum(case when title  ='''+ title +''' then COALESCE(value,'''') end )'''+ title +''''
 from #t where cnt=@count)
set @count=@count+1
End


set @sql= @sql+ ' from test2 group by  [key]'
---print  @sql

exec (@sql)
drop table #t
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help with a query 3 39
Building JSON Results Table FROM DB 9 37
SQL eating up memory? 16 42
T-SQL and CLR parameter strings 9 28
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question