Change list to table - 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 2000 Forums

Transact-SQL (2000)

Change list to table

Author

Topic

greeny122229
Starting Member

25 Posts

Posted - 2010-05-14 : 05:26:31

hi guys,

I have a query i'm trying to do the following, take this table structure:

Record_id Question Detail
344 1 25/05/2010
344 2 mr a text
344 6 10

And run a query to output the data in the following format
record id 1 2 6
344 25/05/2010 mr a text 10

Basically the question becomes a column header with the detail as the value for the corresponding column.

any help appreciated, i'll keep this updated with my own progess

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-14 : 05:30:29

Search for rowset concatenation in google/bing

Madhivanan

Failing to plan is Planning to fail

greeny122229
Starting Member

25 Posts

Posted - 2010-05-14 : 05:42:38

Hi having read the following:
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

i'm still lost as it seems to be all done in 2005 using syntax not available in 2000.

Just to expand on my question,

there could be multiple record id's with varying numbers of questions as such the table need to be built with the maximum amount of questions.

Any further assistance appreciated

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-14 : 06:17:22

quote:
Originally posted by greeny122229

Hi having read the following:
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

i'm still lost as it seems to be all done in 2005 using syntax not available in 2000.

Just to expand on my question,

there could be multiple record id's with varying numbers of questions as such the table need to be built with the maximum amount of questions.

Any further assistance appreciated

Thanks

Read the topic under Scalar UDF with recursion there

Madhivanan

Failing to plan is Planning to fail

greeny122229
Starting Member

25 Posts

Posted - 2010-05-17 : 04:58:02

Ok guys,

couldn't get the recursive function to work as such am endevouring to do this another way, below is where i'm at so far, i know this may not be efficient however there this will only ever be a small record set used by this, also its still a bit messy as i haven't finished yet:

--Get all question id's an account has detail for
DECLARE @questionlist varchar(8000)
declare @columns varchar(8000)
Declare @Len bigint
Declare @col_name varchar(100)
Declare @col_name1 varchar(1000)
Declare @cl bigint
Declare @val_len bigint
Declare @tablecreate varchar(8000)
DECLARE @SQL VARCHAR(1000)

SELECT @questionlist =
COALESCE(@questionlist + ',', '')
+CAST(question_ID AS varchar(5))
from
(select distinct(question_id)
FROM tbl_main_detail where record_id in (select record_id from tbl_main_detail where detail = '19619819191')) a
set @columns = (SELECT @questionlist as 'column_names')
--loop through and identify column names
set @len = len(@columns)
set @col_name = 'default'
set @Val_len = 1
CREATE TABLE #records
(Record_id VARCHAR(32))
insert into #records(record_id)
select distinct record_id from tbl_main_detail where record_id in (select record_id from tbl_main_detail where detail = '19619819191')

while @Val_len <> 0
begin
set @cl = (select LEN(@columns)-LEN(REPLACE(@columns,',','')))
if @cl > 0
begin
set @col_name = (select LEFT(@columns, CHARINDEX(',', @columns,0)-1))
set @col_name1 = (select field_desc from TBL_Questions where question_id = convert(bigint,@col_name))
set @Val_len = (select len(LEFT(@columns, CHARINDEX(',', @columns,0))))
set @columns= (select right(@columns,(@len-(len(@col_name)+1))))
end
else
begin
set @col_name = (select @columns)
set @col_name1 = (select field_desc from TBL_Questions where question_id = convert(bigint,@col_name))
set @Val_len = 0
set @columns= 0
end
set @len = len(@columns)
set @SQL ='ALTER TABLE #records ADD [' + @col_name + '_' + @col_name1+'] varchar (8000)'
EXEC (@SQL)
end
select * from #records
select * from tbl_main_detail
drop table #records

the above creates the follwoing outputs tables:

#Records is a dynamis table and has as many columns as input and has the reocrd id populated.

I now need to somehow insert the values as applicable where record_id and column name match any ideas????

greeny122229
Starting Member

25 Posts

Posted - 2010-05-17 : 05:36:16

cracked it!!! - feel free to use the below concept noting any reference back to this article and me.:

--Define variables
DECLARE @questionlist varchar(8000)
declare @columns varchar(8000)
Declare @Len bigint
Declare @col_name varchar(100)
Declare @col_name1 varchar(1000)
Declare @cl bigint
Declare @val_len bigint
Declare @tablecreate varchar(8000)
DECLARE @SQL VARCHAR(1000)

--get all distinct values for column names (changes it to somma seperated for easy reading)
SELECT @questionlist =
COALESCE(@questionlist + ',', '')
+CAST(question_ID AS varchar(5))
from
(select distinct(question_id)
FROM tbl_main_detail where record_id in (select record_id from tbl_main_detail where detail = '19619819191')) a
set @columns = (SELECT @questionlist as 'column_names')

--Build and populate the temporary table
set @len = len(@columns)
set @col_name = 'default'
set @Val_len = 1
CREATE TABLE #records
(Record_id VARCHAR(32))
insert into #records(record_id)
select distinct record_id from tbl_main_detail where record_id in (select record_id from tbl_main_detail where detail = '19619819191')

while @Val_len <> 0
begin
set @cl = (select LEN(@columns)-LEN(REPLACE(@columns,',','')))
if @cl > 0
begin
set @col_name = (select LEFT(@columns, CHARINDEX(',', @columns,0)-1))
set @col_name1 = (select field_desc from TBL_Questions where question_id = convert(bigint,@col_name))
set @Val_len = (select len(LEFT(@columns, CHARINDEX(',', @columns,0))))
set @columns= (select right(@columns,(@len-(len(@col_name)+1))))
end
else
begin
set @col_name = (select @columns)
set @col_name1 = (select field_desc from TBL_Questions where question_id = convert(bigint,@col_name))
set @Val_len = 0
set @columns= 0
end
set @len = len(@columns)
set @SQL ='ALTER TABLE #records ADD ['+ @col_name1 +'] varchar (8000)'
EXEC (@SQL)
set @SQL ='update #records set ['+@col_name1+'] = b.detail from #records a inner join tbl_main_detail b on b.record_id = a.record_id and b.question_id = ' + @col_name
EXEC (@SQL)
end
select * from #records
drop table #records

greeny122229
Starting Member

25 Posts

Posted - 2010-05-17 : 05:57:39

--Define variables
DECLARE @questionlist varchar(8000)
declare @columns varchar(8000)
Declare @Len bigint
Declare @col_name varchar(30)
Declare @col_name1 varchar(200)
Declare @cl bigint
Declare @val_len bigint
DECLARE @SQL VARCHAR(1000)
declare @accno varchar(20)--variable from user
set @accno = '19619819191'

--get all distinct values for column names (changes it to comma seperated for easy reading)
SELECT @questionlist =
COALESCE(@questionlist + ',', '')
+CAST(question_ID AS varchar(5))
from
(select distinct(question_id)
FROM tbl_main_detail where record_id in (select record_id from tbl_main_detail where detail = @accno)) a
set @columns = (SELECT @questionlist as 'column_names')

--Build and populate the temporary table
set @len = len(@columns)
set @col_name = 'default'
set @Val_len = 1

--Build the table (populating record_id's)
CREATE TABLE #records
(Record_id VARCHAR(32))
insert into #records(record_id)
select distinct record_id from tbl_main_detail where record_id in (select record_id from tbl_main_detail where detail = @accno)

--Loop through all question id's, add the description as column name and populate the applicable values.
while @Val_len <> 0
begin
set @cl = (select LEN(@columns)-LEN(REPLACE(@columns,',','')))
if @cl > 0
begin
set @col_name = (select LEFT(@columns, CHARINDEX(',', @columns,0)-1))
set @col_name1 = (select field_desc from TBL_Questions where question_id = convert(bigint,@col_name))
set @Val_len = (select len(LEFT(@columns, CHARINDEX(',', @columns,0))))
set @columns= (select right(@columns,(@len-(len(@col_name)+1))))
end
else
begin
set @col_name = (select @columns)
set @col_name1 = (select field_desc from TBL_Questions where question_id = convert(bigint,@col_name))
set @Val_len = 0
set @columns= 0
end
set @len = len(@columns)
set @SQL ='ALTER TABLE #records ADD ['+ @col_name +'¬'+ @col_name1 +'] varchar (8000)'
EXEC (@SQL)
set @SQL ='update #records set ['+ @col_name+'¬'+ @col_name1+'] = b.detail from #records a inner join tbl_main_detail b on b.record_id = a.record_id and b.question_id = ' + @col_name
EXEC (@SQL)
end
--Recorset for output to where-ever (asp in my case)
select * from #records

--Remove the temp table
drop table #records

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources