locked
Select Many from one table RRS feed

  • Question

  • Hi,
    I have a problem that I just don’t know how to do. I have the following tables:

    Table 1

    Allowed    Key 
    N             64 
    Y             65 
      
    Table 2
      
    Key      fldname     fldvalue
    64        TEN07     9.827E+18
    64        TEN17     12.11.2009
    65        TEN17     29.11.2005
    65        TEN09     21/05/2012

    Is it possible to have the outcome like the following:

    Allowed  Key        TEN07         TEN17             TEN09
    N           64     9.827E+18   12.11.2009 
    Y           65     29.11.2005                          21/05/2012

    The fldname could be anything from TEN01 to TEN19

    Any help would be greatly appreciated.

    Regards,

    Loftty

    Wednesday, May 9, 2012 8:26 AM

Answers

  • WITH cte

    AS

    (

    SELECT Key,MAX(CASE WHEN fldname ='TEN07'' THEN fldvalue END ) TEN07  ,

    ...........

    FROM tbl GROUP BY Key

    ) SELECT <columns> FROM cte JOIN tb1 ON cte.Key=tb1.Key


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by loftty Wednesday, May 9, 2012 12:00 PM
    Wednesday, May 9, 2012 9:19 AM
    Answerer

All replies

  • Fill this on your own
    select Allowed, Key, (select fldvalue from tab2 where key=a.key and fldname='TEN01') TEN01, (select fldvalue from tab2 where key=a.key and fldname='TEN02') TEN02,...,(select fldvalue from tab2 where key=a.key and fldname='TEN19') TEN19
    from tab1 a
    order by 2;
    go

    Many Thanks & Best Regards, Hua Min

    Wednesday, May 9, 2012 8:40 AM
  • Hi Loftty,

    You'd need to use a Pivot to do this...

    SELECT Allowed, [Key], [TEN01], [TEN02], [TEN03], 
    	   [TEN04], [TEN05], [TEN06], [TEN07], [TEN08], 
    	   [TEN09], [TEN10], [TEN11], [TEN12], [TEN13], 
    	   [TEN14], [TEN15], [TEN16], [TEN17], [TEN18],
    	   [TEN19]
    
    FROM (
          SELECT T1.Allowed,
    			 T2.[Key],
    			 fldname,
    			 fldvalue
    
    	  FROM Table1 T1
    		   JOIN Table2 T2 ON T1.[Key] = T2.[Key]
         ) AS src
         
         PIVOT 
         
         (
          MAX([fldvalue]) FOR fldname IN ([TEN01], [TEN02], [TEN03],
    									  [TEN04], [TEN05], [TEN06],
    									  [TEN07], [TEN08], [TEN09],
    									  [TEN10], [TEN11], [TEN12],
    									  [TEN13], [TEN14], [TEN15],
    									  [TEN16], [TEN17], [TEN18],
    									  [TEN19])
         ) AS pvt
    

    Give that a go...


    Zach Stagers - http://www.scratchbox.co.uk
    Remember to Mark as Answer and Vote as Helpful

    Wednesday, May 9, 2012 9:00 AM
  • WITH cte

    AS

    (

    SELECT Key,MAX(CASE WHEN fldname ='TEN07'' THEN fldvalue END ) TEN07  ,

    ...........

    FROM tbl GROUP BY Key

    ) SELECT <columns> FROM cte JOIN tb1 ON cte.Key=tb1.Key


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by loftty Wednesday, May 9, 2012 12:00 PM
    Wednesday, May 9, 2012 9:19 AM
    Answerer
  • Hi,

    try below code.

    begin
    declare @t1 as table(allowed char(1),keyy int)
    insert into @t1(allowed,keyy)
    values('N',64);
    insert into @t1(allowed,keyy)
    values('Y',65);
    select * from @t1;
    
    
    declare @t2 as table(keyy int,fldname varchar(20),fldvalue varchar(20))
    insert into @t2(keyy,fldname,fldvalue)
    values(64,'TEN07','9.827E+18')
    insert into @t2(keyy,fldname,fldvalue)
    values(64,'TEN17','12.11.2009')
     insert into @t2(keyy,fldname,fldvalue)
     values(65,'TEN17','29.11.2005')
     insert into @t2(keyy,fldname,fldvalue)
     values(65,'TEN09','21/05/2012')
     
     select * from @t2;
     
     select keyy,TEN07,TEN17,TEN09
     from (select keyy,fldname,fldvalue
     from @t2) t2
     PIVOT (max(fldvalue) for fldname in (TEN07,TEN17,TEN09)) as pvt
     order by keyy
    
    end


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Wednesday, May 9, 2012 11:06 AM
  • Thank you for all answers and help managed o get it working with Uri Dimant answer.

    Thanks,

    Loftty

    Wednesday, May 9, 2012 12:01 PM