Select

  • 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

  • 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.

     

  • GReat.Thank you very much.

  • 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