Chris writes "Hi,I’ve seen several solutions to convert rows to comma delimited lists, but I need to be able to convert rows to columns, lots of columns. My source data is a table of points organized by item_no, point_no (0-N), and point_value. I also have a meta data table that maps the item_no, point_no into physical locations on the item. I have many items and each item has a different number of points and a different mapping for each point. For my first approach I decided to use dynamic sql and came up with the following:Example table_of_pts:Item_no point_no value‘00001’ 0 10‘00001’ 1 9‘00001’ 2 12‘00001’ 3 9‘00001’ 4 11‘00001’ 0 13‘00001’ 1 8‘00001’ 2 11‘00001’ 3 10‘00001’ 4 10‘00002’ 0 110‘00002’ 1 89‘00002’ 2 132
Example tbl_of_meta_ptsItem_no point_no loc‘00001’ 0 F1‘00001’ 1 F2‘00001’ 2 B1‘00001’ 3 B2‘00001’ 4 C‘00002’ 0 F1‘00002’ 1 B1‘00002’ 2 C
CREATE PROCEDURE dbo.my_proc@item_no as varchar(5)ASBEGINdeclare @select as varchar(8000)declare @from as varchar(8000)declare @where as varchar(8000)select @select = COALESCE(@select + ', T' + CAST(POINT as varchar(5)) + '.[value] P', 'SELECT ' + char(13) + 'T0.item_no, T0.sn, T0.moldsheet, T0.date, T0.[value] P') + CAST(POINT as varchar(5))from tbl_of_meta_ptswhere item_no = @item_no
select @from = COALESCE(@from + ',' + char(13) + 'fn_pt(' + char(39) + @item_no + char(39) + ', ' + CAST(POINT as varchar(5)) + ') T', char(13) + 'FROM' + char(13) + 'fn_pt(' + char(39) + @item_no + char(39) + ', ' + CAST(POINT as varchar(5)) + ') T') + CAST(POINT as varchar(5))from tbl_of_meta_ptswhere item_no = @item_no
select @where = COALESCE(@where + 'and T0.SN = T' + CAST(POINT as varchar(5)) + '.SN ', char(13) + 'WHERE' + char(13) + 'T0.SN = T' + CAST(POINT as varchar(5)) + '.SN ')from tbl_of_meta_ptswhere item_no = @item_no
--print @select + @from + @whereexec( @select + @from + @where )END
fn_pt is a short hand function, all it does is:CREATE FUNCTION dbo.fn_pt (@ITEM_NO as varchar(5), @POINT as smallint) RETURNS table AS RETURN SELECT * FROM tbl_of_pts WHERE POINT = @POINT AND ITEM_NO = @ITEM_NO
This works for about 120 points or so before I exceed the 8000 character limit, but some of the items have 300-400+ points. Ideally I’d like to be able to perform aggregate functions as part of the query too. In the above case I need to dump the result to excel and then do my calculations there. I’d be able to do this if I could do the above as a function, but since I use the ‘exec’ command, I can’t put this in a function. An alternate thought was to put all the points in one record and just pad out the table with enough columns to fit the biggest item, but this gets very messy when trying to map points to locations. Am I just going about this all wrong, is there a better way to store the data? Any help would make my life much easier. Thank you!"
Converting rows to columns 
Posted - 2005-03-30 : 07:54:11
