March 31, 2004 at 1:34 pm
Hi,
I need help in selecting the data in the following desired way.
For example I have a table called
Demo defined as follows
[id] [char] (22) ,
[ques_name] [varchar] (50) ,
[ques_val] [varchar] (100) )
and the data is like
1,date,10/14/2004
1,countyr,usa
1,zip,20191
1,email,test@yahoo.com
1,name,test
I want to retreive the data as
id,date,country,zip,email,name
1,10/14/2004,usa,20191,test@yahoo.com,test.
Basically i want the column2 values as actual columns with ques_val as the data for that column.
So it's like collapsing the data into single row rather than as 5 rows.
Any help is higly appreciated.
Thanks,
MK
March 31, 2004 at 1:47 pm
I have used this before on simpler issues but this is how you do what you are asking.
Simple do like so.
SELECT
[id],
MAX(CASE ques_name WHEN 'date' THEN ques_val ELSE null END) AS [date],
MAX(CASE ques_name WHEN 'country' THEN ques_val ELSE null END) AS country,
MAX(CASE ques_name WHEN 'zip' THEN ques_val ELSE null END) AS zip,
MAX(CASE ques_name WHEN 'email' THEN ques_val ELSE null END) AS email,
MAX(CASE ques_name WHEN 'name' THEN ques_val ELSE null END) AS [name]
FROM
tblName
GROUP BY
[ID]
ORDER BY
[ID]
You could even get fancy and cast date and zip and such to better datatypes inside the max.
March 31, 2004 at 3:31 pm
GReat.Thank you very much.
April 1, 2004 at 3:23 pm
Or, if you want the columns to be dynamic based on distinct values in ques_name:
----------------------------------------------------------------
if object_ID('tempdb..#Test') is not null drop table #Test
Create Table #Test
([id] [char] (22) ,
[ques_name] [varchar] (50) ,
[ques_val] [varchar] (100) )
Insert #Test Values ('1','date','10/14/2004')
Insert #Test Values ('1','country','usa')
Insert #Test Values ('1','zip','20191')
Insert #Test Values ('1','email','test@yahoo.com')
Insert #Test Values ('1','name','test')
Insert #Test Values ('2','date','10/14/2005')
Insert #Test Values ('2','country','usa2')
Insert #Test Values ('2','zip','20192')
Insert #Test Values ('2','email','test@yahoo.com2')
Insert #Test Values ('2','name','test2')
-------------------------------------------------------------
if object_ID('tempdb..#Column') is not null drop table #Column
create Table #Column (ColName sysname)
if object_ID('tempdb..#Row') is not null drop table #Row
create Table #Row ([ID] char(22))
declare @ColName sysname
Insert #Column(ColName)
Select distinct ques_name
From #Test
Insert #Row ([ID])
Select distinct [id]
from #Test
While 1 = 1
BEGIN
Select top 1
@ColName = ColName
From #Column
Where ColName > isnull(@ColName, '')
order by ColName
if @@RowCount = 0 Break
exec('Alter Table #Row add ' + @ColName + ' varchar(100)')
exec('Update r
set ' + @ColName + ' = ques_val
From #Row r
JOIN #Test t on r.[ID] = t.[ID]
where t.ques_name = ''' + @ColName + '''')
END
select * from #Row
Signature is NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply