none
Put table rows into column RRS feed

  • Question

  • Hi,

    I have the following rows in an MS Access 2010 table  

    key name attribute value

    1  V1 color red

    2  V1 weight heavy

    3  V2 color yellov

    4  V2 weight light

    How to select data to get:

    V1 red heavy

    V2 yellow light


    Thanks in advance,


    Michel


    • Edited by Michel777 Wednesday, October 30, 2013 8:50 PM
    Wednesday, October 30, 2013 8:48 PM

Answers

  • Try this:


    Declare @Table  table (Keys Int,Name Varchar(10),Attribute Varchar(50),Value Varchar(50))
    
    Insert Into @Table Values(1 , 'V1','colour', 'red')
    
    ,(2  ,'V1' ,'weight' ,'heavy')
    
    ,(3  ,'V2' ,'Color' ,'yellov')
    
    ,(4  ,'V2' ,'weight' ,'light')
    
    
    Select Distinct Name,
                    STUFF((     SELECT ','+temp.Value
    
                                    FROM @Table temp
                                    WHERE  temp .Name = Main.Name
                                    FOR XML PATH('')), 1, 1, '' )
                AS Value
    From @Table Main


    • Marked as answer by Michel777 Saturday, November 2, 2013 7:10 PM
    Wednesday, October 30, 2013 7:15 PM
  • Select name,
      Max(Case When attribute = 'Color' Then value End),
      Max(Case When attribute = 'weight' Then value End)
    From <your table>
    Group By name
    Order By name;
    

    I'm not sure whether it was a typo or you really meant to allow both color and colour as specifying color.  If the latter, you could do

    Select name,
      Max(Case When attribute In ('Color','Colour') Then value End),
      Max(Case When attribute = 'weight' Then value End)
    From <your table>
    Group By name
    Order By name;

    Tom

    • Marked as answer by Michel777 Saturday, November 2, 2013 7:10 PM
    Wednesday, October 30, 2013 7:17 PM
  • It would be easier if you used consistent values for color.

    declare @tbl table (id varchar(4), name varchar(30), val varchar(20));
    insert @tbl
    values
    ('V1', 'colour', 'red'),
    ('V1', 'weight', 'heavy'),
    ('V2', 'Color', 'yellov'),
    ('V2', 'weight', 'light');
    
    with cte as (
    select id
    , case when name = 'colour' then 'color' else name end as name
    , val
    from @tbl
    )
    select pvt.*
    from cte 
    pivot (max(val) for name in ([Color], [weight])
    
    ) as pvt


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Michel777 Saturday, November 2, 2013 7:10 PM
    Wednesday, October 30, 2013 7:17 PM
  • See: Concatenate values from related records (Allen Browne)

    Select distinct    
       name    
       ,ConcatRelated("Value", "tblOrders", "name = """ & [Name] & """")
    FROM   
        YourTableName


    Michał


    • Edited by Dziubek Michał Wednesday, October 30, 2013 10:25 PM
    • Marked as answer by Michel777 Saturday, November 2, 2013 7:10 PM
    Wednesday, October 30, 2013 10:24 PM
  • If you absolutely sure that every [name] instance has exactly 2 records for the 2 attributes, you can use a Query with the SQL String:

    SELECT C1.[name], C1.Value AS ColourAttrib, C2.Value AS WeightAttrib
    FROM [YourTable] AS C1
      INNER JOIN [YourTable] AS C2
      ON C1.[name] = C2.[name]  
    WHERE (C1.attribute = "color")
      AND (C2.attribute = "weight")

    to get the 3 required columns as per your question.

    BTW, "name" is very bad custom Field name since every object in Access has the Property "Name" also and this can be very confusing later in the database development.  I would also avoid "attribute" and "value" as custom Field names also.


    Van Dinh


    • Edited by Van Dinh Thursday, October 31, 2013 2:15 AM Addendum
    • Marked as answer by Michel777 Saturday, November 2, 2013 7:10 PM
    Thursday, October 31, 2013 2:12 AM

All replies

  • Hi,

    I have the following rows in a table

    key name attribute value

    1  V1 colour red

    2  V1 weight heavy

    3  V2 Color yellov

    4  V2 weight light

    How to select data to get:

    V1 red heavy

    V2 yellow light


    Thanks in advance,


    Michel

    Wednesday, October 30, 2013 7:02 PM
  • Try this:


    Declare @Table  table (Keys Int,Name Varchar(10),Attribute Varchar(50),Value Varchar(50))
    
    Insert Into @Table Values(1 , 'V1','colour', 'red')
    
    ,(2  ,'V1' ,'weight' ,'heavy')
    
    ,(3  ,'V2' ,'Color' ,'yellov')
    
    ,(4  ,'V2' ,'weight' ,'light')
    
    
    Select Distinct Name,
                    STUFF((     SELECT ','+temp.Value
    
                                    FROM @Table temp
                                    WHERE  temp .Name = Main.Name
                                    FOR XML PATH('')), 1, 1, '' )
                AS Value
    From @Table Main


    • Marked as answer by Michel777 Saturday, November 2, 2013 7:10 PM
    Wednesday, October 30, 2013 7:15 PM
  • Select name,
      Max(Case When attribute = 'Color' Then value End),
      Max(Case When attribute = 'weight' Then value End)
    From <your table>
    Group By name
    Order By name;
    

    I'm not sure whether it was a typo or you really meant to allow both color and colour as specifying color.  If the latter, you could do

    Select name,
      Max(Case When attribute In ('Color','Colour') Then value End),
      Max(Case When attribute = 'weight' Then value End)
    From <your table>
    Group By name
    Order By name;

    Tom

    • Marked as answer by Michel777 Saturday, November 2, 2013 7:10 PM
    Wednesday, October 30, 2013 7:17 PM
  • It would be easier if you used consistent values for color.

    declare @tbl table (id varchar(4), name varchar(30), val varchar(20));
    insert @tbl
    values
    ('V1', 'colour', 'red'),
    ('V1', 'weight', 'heavy'),
    ('V2', 'Color', 'yellov'),
    ('V2', 'weight', 'light');
    
    with cte as (
    select id
    , case when name = 'colour' then 'color' else name end as name
    , val
    from @tbl
    )
    select pvt.*
    from cte 
    pivot (max(val) for name in ([Color], [weight])
    
    ) as pvt


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Michel777 Saturday, November 2, 2013 7:10 PM
    Wednesday, October 30, 2013 7:17 PM
  • Hi Tom, thanks a lot for your effort !

    It was a typo.... I got an  error message "Syntax error (missing Operator)  in query Expression 'Max( Case.....End)'.

    I need that query for MS Access 2010. May be I'm wrong here, in this Forum ? Should I seek for a MS Access Forum ?


    Thanks,


    Michel


    • Edited by Michel777 Wednesday, October 30, 2013 8:20 PM
    Wednesday, October 30, 2013 8:19 PM
  • You need to find an MS Access forum.  I seem to recall a crosstab query type in Access that you can use.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, October 30, 2013 8:22 PM
  • See: Concatenate values from related records (Allen Browne)

    Select distinct    
       name    
       ,ConcatRelated("Value", "tblOrders", "name = """ & [Name] & """")
    FROM   
        YourTableName


    Michał


    • Edited by Dziubek Michał Wednesday, October 30, 2013 10:25 PM
    • Marked as answer by Michel777 Saturday, November 2, 2013 7:10 PM
    Wednesday, October 30, 2013 10:24 PM
  • I need that query for MS Access 2010. May be I'm wrong here, in this Forum ? Should I seek for a MS Access Forum ?

    Yes, but more than that, you are not in the RDBMS world! You have no idea what a normal frm of any kind is. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, October 31, 2013 12:09 AM
  • If you absolutely sure that every [name] instance has exactly 2 records for the 2 attributes, you can use a Query with the SQL String:

    SELECT C1.[name], C1.Value AS ColourAttrib, C2.Value AS WeightAttrib
    FROM [YourTable] AS C1
      INNER JOIN [YourTable] AS C2
      ON C1.[name] = C2.[name]  
    WHERE (C1.attribute = "color")
      AND (C2.attribute = "weight")

    to get the 3 required columns as per your question.

    BTW, "name" is very bad custom Field name since every object in Access has the Property "Name" also and this can be very confusing later in the database development.  I would also avoid "attribute" and "value" as custom Field names also.


    Van Dinh


    • Edited by Van Dinh Thursday, October 31, 2013 2:15 AM Addendum
    • Marked as answer by Michel777 Saturday, November 2, 2013 7:10 PM
    Thursday, October 31, 2013 2:12 AM
  • Uh, guys, isn't the for SQL Server.  I know some things are fully compatible, but I don't think this is.  Please correct me if I'm wrong!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, November 20, 2013 4:29 AM