Solved

Using table data as headers

Posted on 2006-07-15
11
465 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
  • 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
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 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:EugeneZ
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:
EugeneZ 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL tables, .net application and ETL connundrum 3 55
SSRS 2013 - Overlapping reports 2 21
insert wont work in SQL 14 21
SQL2016 to ORACLE11G linked-server 6 12
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

770 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