none
SQL Select - Select multiple values from one column

    Question

  • Hello all.

    I hope someone can offer me some help.  I'm trying to construct a SQL statement that will select at least two values from one column.
    But i have no idea how to solve this problem. Here is my problem in detail:

    My Table "Example" looks like this:


    article

    position

    value

    101

    1

    ONE

    101

    2

    TWO

    101

    4

    FOUR

    101

    18

    ONEEIGHT

    101

    25

    TWOFIVE

    102

    1

    ONE

    102

    2

    TWO

    102

    4

    FOUR

    106

    1

    ONE

    107

    1

    ONE

    107

    2

    TWO

    107

    4

    FOUR

    107

    25

    TOWFIVE



    First i need the value of article 101 with position 1:

    input = 101

    SELECT article, value FROM example WHERE article = input AND positon = 1

     The result looks like this:

    article

    value

    101

    ONE

    No problem so far.


    Now, i need not only the value of position 1 in this row, but also the value of position 18 for article 101.

    A simple PSEUDO-CODE for this would be:

    SELECT article, value FROM example WHERE article = input AND positon = 1 AND positon = 18

     

    The Result should be something like this:

    article

    value

    value

    101

    ONE

    ONEEIGHT




    I hope this provides enough information for my problem. I am new to SQL and hope you can give me some ideas to solve this problem.
    Thank you in advance.
    Friday, October 21, 2011 8:50 AM

Answers

  • DECLARE @example table (article int, position int, value varchar(100))
    
    INSERT @example
    SELECT 101,1,'ONE'
    UNION ALL SELECT 101,2,'TWO'
    UNION ALL SELECT 101,4,'FOUR'
    UNION ALL SELECT 101,18,'ONEEIGHT'
    UNION ALL SELECT 101,25,'TWOFIVE'
    UNION ALL SELECT 102,1,'ONE'
    
    SELECT
    	article
    	,[1]=MAX(CASE WHEN position=1 THEN value END)
    	,[18]=MAX(CASE WHEN position=18 THEN value END)
    FROM
    	@example E
    WHERE
    	article=101
    GROUP BY
    	article
    


    Or you can use PIVOT if you are using SQL Server 2005 or later
    SELECT *
    FROM
    	@example E
    	PIVOT (MAX(value) FOR position IN ([1],[18])) pvt
    WHERE
    	article=101
    


    • Proposed as answer by DVR Prasad Friday, October 21, 2011 9:38 AM
    • Marked as answer by KJian_ Friday, October 28, 2011 6:52 AM
    Friday, October 21, 2011 9:16 AM

All replies

  • DECLARE @example table (article int, position int, value varchar(100))
    
    INSERT @example
    SELECT 101,1,'ONE'
    UNION ALL SELECT 101,2,'TWO'
    UNION ALL SELECT 101,4,'FOUR'
    UNION ALL SELECT 101,18,'ONEEIGHT'
    UNION ALL SELECT 101,25,'TWOFIVE'
    UNION ALL SELECT 102,1,'ONE'
    
    SELECT
    	article
    	,[1]=MAX(CASE WHEN position=1 THEN value END)
    	,[18]=MAX(CASE WHEN position=18 THEN value END)
    FROM
    	@example E
    WHERE
    	article=101
    GROUP BY
    	article
    


    Or you can use PIVOT if you are using SQL Server 2005 or later
    SELECT *
    FROM
    	@example E
    	PIVOT (MAX(value) FOR position IN ([1],[18])) pvt
    WHERE
    	article=101
    


    • Proposed as answer by DVR Prasad Friday, October 21, 2011 9:38 AM
    • Marked as answer by KJian_ Friday, October 28, 2011 6:52 AM
    Friday, October 21, 2011 9:16 AM
  • Hi  Someone1 !

    Lets try this out;

     

    SELECT Article , Value FROM Example 
    WHERE Article = 101 AND Position IN(1,18)



     


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks, Hasham

     

    Friday, October 21, 2011 10:30 AM
    Answerer