0

What I am trying to do is create a temp table in SQL Server 2008 that uses the column names from resultset

For example: this is what i get form my result set:

Account weight  zone
 22      5       1
 23      3       2
 22      5       1
 23      3       2
 24      7       3

From this result set, Zone column values should be converted to dynamic column based on the zone count such as

Account weight  zone 1 zone 2 zone 3
 22      5       2 
 23      3               2
 24      7                     1

Please help me?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2302158
  • 417
  • 2
  • 6
  • 14

3 Answers3

1

You could use PIVOT:

SELECT Account, Weight, [1] AS Zone1, [2] AS Zone2, [3] AS Zone3, [4] AS Zone4
FROM AccountWeight
PIVOT
(
   COUNT(Zone) FOR Zone IN ([1], [2], [3], [4])
) AS ResultTable
ORDER BY Account

See SQL Demo. Also, you can find that interesting: Efficiently convert rows to columns in sql server

Community
  • 1
  • 1
Ruslan Veselov
  • 337
  • 1
  • 10
0
select account, weight
       sum(case when zone = 1 then 1 end) as zone1,
       sum(case when zone = 2 then 1 end) as zone2,
       sum(case when zone = 3 then 1 end) as zone3
from your_table
group by account, weight
juergen d
  • 201,996
  • 37
  • 293
  • 362
0

You can solve this problem by using dynamic sql query. Go through some very good posts on dynamic pivoting...

SQL Server 2005 Pivot on Unknown Number of Columns

Pivot Table and Concatenate Columns

Community
  • 1
  • 1
ram_sql
  • 404
  • 2
  • 9