tsql help colunn to rows

[lendbz]

Hello,

I have a question:

I have a Table of certain items

create table item (
IdItem int,
Item varchar(64),
...
);

a lookuptable which defines some properties

create table property (
IdProperty int,
Property varchar(64)
);

and a table which defines the different properties which are possible for
a certain item:

create table tlkp_item_property (
IdItem int,
IdProperty int
);

So I can easily select all the properties of a certain item in a table
where the columns contain the properties. But I want to have an output
of the kind

Item 1, Property 1, Property 2, Property 3, ...
Item 2, <Properties of Item 2>
...

Any idea of how I would do this???

Thanks in advance

lendbz

 

[vbkris]

hmm, this is very difficult to do because each item will have different properties.

therefore otuput could be:

Item1 Property1 Property2 NULL
Item2 NULL Property2 Property3

is that ok? if yes, then you will have use cursors...

Known is handfull, Unknown is worldfull

 

[lendbz]

That's ok How would i do this with cursors???

 

[vbkris]

u will have to open the properties table with a cursor, something like this:



declare @sql varchar(8000),@IdProperty int
set @sql='select distinct IdItem, '
DECLARE Cursor CURSOR FOR
SELECT distinct IdProperty
FROM tlkp_item_property

OPEN Cursor
FETCH NEXT FROM Cursor into @IdProperty

set @sql=@sql+'(select Property from property where IdProperty='+cast(@IdProperty as varchar)+'),'
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Cursor into @IdProperty

set @sql=@sql+'(select Property from property where IdProperty='+cast(@IdProperty as varchar)+'),'
END
set @sql=@sql+'1 from tlkp_item_property'
--this will give you a list of select queries for the columns (i have added one more column called 1, but u will have to do a substring operation to remove the last ,).

print(@sql)
CLOSE Cursor
DEALLOCATE Cursor


Known is handfull, Unknown is worldfull