asked on

Pivot table in sql 2008

Hi,

I am not able to use pivot query to produce desired result. Please help me to get reqult that I want using Pivot query. I have added the table script and data in the code Snippet. I want to display the information like table given as follow using Pivot query.
Qty_Rang      red        green blue        black
0-10             12        13        14        15

10-20             16        17        18 -

20-30             20        21        22 -
Column names ( red, green blue....) are not fix it can increase or decrease. I need a dynamic Pivot query for this.

thanks,
waiting for your reply.

CREATE TABLE [dbo].[testPivo](
	[pid] [int] IDENTITY(1,1) NOT NULL,
	[qty_from] [int] NOT NULL,
	[qty_to] [int] NOT NULL,
	[color] [varchar](50) NOT NULL,
	[price] [decimal](9, 2) NULL,
 CONSTRAINT [PK_testPivo_1] PRIMARY KEY CLUSTERED 
(
	[pid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



insert into testPivo values(0,10,'red',12)
insert into testPivo values(0,10,'green',13)
insert into testPivo values(0,10,'blue',14)
insert into testPivo values(10,20,'red',16)
insert into testPivo values(10,20,'green',17)
insert into testPivo values(10,20,'blue',18)
insert into testPivo values(20,30,'red',20)
insert into testPivo values(20,30,'green',21)
insert into testPivo values(20,30,'blue',22)
insert into testPivo values(0,10,'black',15)

Open in new window

ASKER CERTIFIED SOLUTION

rashmi_vaghela

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gurbirs

ASKER

Thanks alot . I have made a very little change according to my requirement.

Thanks alot once again.

DECLARE @Columns varchar(8000)

DECLARE @SQL varchar(8000)
SET @Columns = substring((select ',['+color+']' from testPivo group by color for xml path('')),2,8000)
SET @SQL = 'SELECT * FROM 
  (Select (CAST(qty_from as varchar)+''-''+ cast(qty_to as varchar))as Qty_Rang ,COLOR,qty_to,price from testPivo) sourcedata
PIVOT
  (max(price) for color in ('+@Columns+')) pivottable'
  print @sql
  
EXEC(@sql)
GO

Open in new window