?
Solved

Using table data as headers

Posted on 2006-07-15
11
Medium Priority
?
484 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 93

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 93

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 43

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 200 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 93

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 93

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 43

Accepted Solution

by:
Eugene Z earned 800 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

752 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