make data as column name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

make data as column name

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 21:06:37

Hi,
I got this solution from old forum but it was locked.
I need help on modifying below solution that KHTAN and Madhivanan provided.. here is my query and expected result.
their solution managed to get the row as columnname, but i need the same data to be populated under respective column header.
thanks in advance

declare @t table(num int)
insert into @t
select 101 union all
select 102 union all
select 102 union all
select 103
select num from @t
declare @s varchar(8000)
select @s = COALESCE(@s + ',', '') + cast(num as varchar(10)) + 'col'+cast(num as varchar(10))
from @t
exec('select '+@s)

--expected result
select 101 as col101, 102 as col102, 103 as col103 union all
select null as col101, 102 as col102, null as col103

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-25 : 21:27:55

What is the version of SQL Server you are using ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-25 : 21:37:12

this is for SQL 2005/2008


CREATE TABLE #t (num int)
INSERT INTO #t
SELECT 101 UNION ALL
SELECT 101 UNION ALL
SELECT 102 UNION ALL
SELECT 102 UNION ALL
SELECT 102 UNION ALL
SELECT 103 UNION ALL
SELECT 103 UNION ALL
SELECT 105

DECLARE @col_list varchar(MAX)
SELECT  @col_list       = ISNULL(@col_list + ',', '') + QUOTENAME(CONVERT(varchar(10), num))
FROM    #t
GROUP BY num

DECLARE @sql    nvarchar(MAX)
SELECT  @sql    = '
SELECT  ' + @col_list + '
FROM    (
                SELECT  *, 
                        row_no = row_number() OVER (PARTITION BY num ORDER BY num)
                FROM    #t
        ) d
        pivot
        (
                SUM(num)
                FOR num IN ( ' + @col_list + ')
        ) p'

print   @sql
EXEC    (@sql)

KH
[spoiler]Time is always against us[/spoiler]

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 22:09:36

thanks KH, it worked perfectly.
But which part to fill in the column with data

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 22:22:31

ok actually my problem is to display below 2 tables in 1:

table ww
--------
ww
201035
201036
201037

table x
--------
spec ww qty
DLR 201035 100
DLR 201036 200
DLR 201037 300
SBR 201035 100

expected result
---------------
spec 201035 201036 201037
DLR 100 200 300
SBR 100 null null

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-25 : 22:37:12

the problem is i cannot use temp table, for some reason from the codebehind it failed to fetch the data if i use temp table... here's the sample table and expected result :-
declare @tbl1 table(ww int)
insert into @tbl1
select 201035 union all
select 201036 union all
select 201037
select ww from @tbl1

declare @tbl2 table(spec varchar(10), ww int, qty int)
insert into @tbl2
select 'DLR' as spec,201035 as ww, 100 as qty union all
select 'DLR' as spec,201036 as ww, 200 as qty union all
select 'DLR' as spec,201037 as ww, 300 as qty union all
select 'SBR' as spec,201035 as ww, 100 as qty
select spec, ww, qty from @tbl2

--expected result ; how to put column name to just 2010xx?
declare @tbl3 table(spec varchar(10), c201035 int, c201036 int, c201037 int)
insert into @tbl3
select 'DLR',100,200,300 union all
select 'SBR',100, null , null
select * from @tbl3

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-25 : 23:29:23

"the problem is i cannot use temp table"
what do you mean exactly ?

the temp table in my post are just for illustration and testing purposes. Replace that with your actual table name

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-25 : 23:32:04

[code]
create table #ww (ww int)
insert into #ww
select 201035 union all
select 201036 union all
select 201037

create table #x (spec varchar(3), ww int, qty int)
insert into #x
select 'DLR', 201035, 100 union all
select 'DLR', 201036, 200 union all
select 'DLR', 201037, 300 union all
select 'SBR', 201035, 100

-- the QUERY
declare @col_list varchar(max)
select @col_list = isnull(@col_list + ',', '') + quotename(convert(varchar(10), ww))
from #ww
group by ww

declare @sql nvarchar(max)
select @sql = '
select *
from #x
pivot
(
sum(qty)
for ww in ( ' + @col_list + ')
) p'

print @sql
exec (@sql)[/code]

KH
[spoiler]Time is always against us[/spoiler]

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-26 : 00:56:43

KH> awesome, but i couldnt figure out how to Not select * from table #x, because i have unique guid.. when i group it, the whole WW table disappear :(

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-26 : 02:51:05

change to the section in red and specify the column instead of *


select	*
from	(
            select spec, ww, qty
            from   #x
        ) d
	pivot
	(
		sum(qty)
		for ww in ( ' + @col_list + ')
	) p'

KH
[spoiler]Time is always against us[/spoiler]

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-26 : 03:10:14

KH...,
im sorry, i looked at wrong data.. it populate the data correctly...phew :0
thanks so much for your help... u r my saver!

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-26 : 03:31:28

you are welcome. .

even if the 1st query works for you, you should also change to the amend one (in red), the 1st one will break your query when you add a column to the table.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources