Solved

Using table data as headers

Posted on 2006-07-15
11
456 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Update foreign key reference after insert 9 36
Numeric sequence in SQL 14 38
Group by and order by clause 28 36
Mssql SQL query 14 28
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now