dynamic crosstab problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

dynamic crosstab problem

Author

Topic

dirac
Starting Member

12 Posts

Posted - 2008-01-16 : 10:37:38

here is my problem:

i have just one table, named "collector" and the columns are:

ledno(nvarchar(50))' the item number of a kind of led(a mini light source) which we use
ledname(nvarchar(50))' the name of the leds
quantity(int) ' how many leds we use on that part
partname(nvarchar(50) 'the parts name

and i want it to be this way;

ledno ledname partname(part name goes dynamically according to our records(the header))
202.a led-a 3 '(this is the total led-a used in the correct partname)

205.b led-b 6'(this is the total led-b used in the correct partname)

and the "partname" header musn't repeat itself , i hope i am able to tell my problem, i know it's simple for you but i couldn't manage may self, thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 10:47:23

smoething like this should do the trick (i've not tested this)

DECLARE @partname varchar(1000),@Sql varchar(8000)

SELECT @partname = COALESCE(@partname,partname + ',') AS text()
FROM collector
FOR XML PATH('')

DECLARE @Sql='SELECT * FROM collector c PIVOT (SUM(quantity) FOR partname IN (''' + REPLACE(LEFT(@partname,LEN(@partname)-1),',',''',''') + ''')p'

EXEC (@Sql)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources