How to get multiple values from a singe query in sql

Question

I have a query like:

SELECT NUM,
       PARAM_1 
FROM STOCK_CARD_PARAMETER WITH(NOLOCK) 
WHERE FK_STOCK_CARD IN (SELECT FK_sTOCK_CARD 
                        FROM STOCK_BARCODE WITH(NOLOCK) 
                        WHERE BARCODE = '2002002232364' )

And it returns:

 NUM     PARAM_1
 1       İNDİRİMSİZ
 2       SEZON
 3       UNISEXYETISKIN

I have to get a result like that. (side by side)

İNDİRİMSİZ  SEZON UNISEXYETISKIN

To do this, I execute almost the same query 2 times like this:

INDIRIM = (SELECT PARAM_1 
           FROM STOCK_CARD_PARAMETER WITH(NOLOCK) 
           WHERE FK_STOCK_CARD IN (SELECT FK_sTOCK_CARD 
                                   FROM STOCK_BARCODE WITH(NOLOCK) 
                                   WHERE BARCODE = @BARKOD  AND NUM = 1)),
STATU = ( SELECT PARAM_1 
          FROM STOCK_CARD_PARAMETER WITH(NOLOCK) 
          WHERE  FK_STOCK_CARD IN (SELECT FK_sTOCK_CARD 
                                   FROM STOCK_BARCODE WITH(NOLOCK) 
                                   WHERE BARCODE = @BARKOD AND NUM = 2))

NUM column is the key here. How do I make this two query combined and get the result side by side?

Results format on one line is ok for your example, but what happens then it returns several thousand "rows"? String them out in a single line? The display is at the mercy of those who put data into the tables. Who, being humans, will do unexpected things. — donPablo
– donPablo, Commented May 25, 2015 at 6:24

sqluser · Accepted Answer · 2015-05-25 06:21:13Z

3

Using PIVOT

SELECT  [1], [2], [3]
FROM
(SELECT NUM, PARAM_1 
    FROM table) AS SourceTable
PIVOT
(
MAX(PARAM_1)
FOR NUM IN ([1], [2], [3])
) AS PivotTable;

Sample data with output

WITH C(NUM, PARAM_1) AS(
    SELECT 1 ,  N'İNDİRİMSİZ' UNION ALL
    SELECT 2 ,  N'SEZON' UNION ALL
    SELECT 3 ,  N'UNISEXYETISKIN'
)

SELECT  [1], [2], [3]
FROM
(SELECT NUM, PARAM_1 
    FROM C) AS SourceTable
PIVOT
(
MAX(PARAM_1)
FOR NUM IN ([1], [2], [3])
) AS PivotTable;

Output

İNDİRİMSİZ  SEZON   UNISEXYETISKIN

answered May 25, 2015 at 6:21

sqluser

5,7127 gold badges44 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get multiple values from a singe query in sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related