Need help in writing query to show data from rows into columns

Question

Post reply

Need help in writing query to show data from rows into columns

ramineni.suresh661

Mr or Mrs. 500

Points: 554
More actions
June 1, 2016 at 9:41 pm

#310919

Hi All,
I'm new to SQL , I'm facing issue in converting data from rows into columns ,Can some one please help in getting the results as shown in the following query.
Any help would be highly appreciated
CREATE TABLE #temp
(
STORE_ID INT
,PRINTER_NM VARCHAR(50)
,PRIORITY_IND INT
,PRINTER_ID INT
)
INSERT INTO #temp
values(1000,'PRINT1',1,100)
,(1000,'PRINT2',2,101)
,(1001,'PRINT1',1,102)
,(1002,'PRINT2',2,103)
,(1002,'PRINT1',1,104)
,(1002,'PRINT2',2,105)
,(1003,'PRINT1',1,106)
,(1003,'PRINT2',2,107)
SELECT 1000 AS 'STORE_ID'
,'PRINT1' AS PRIMARYPRINTER_NM
, 100 AS PRIMARYPRINTER_ID
,'PRINT2' AS SECONDARYPRINTER_NM
,101 AS SECONDARYPRINTER_ID
union All
SELECT 1001 AS 'STORE_ID'
,'PRINT1' AS PRIMARYPRINTER_NM
, 102 AS PRIMARYPRINTER_ID
,'PRINT2' AS SECONDARYPRINTER_NM
,103 AS SECONDARYPRINTER_ID
drop table #temp

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 1

Quick suggestion

😎

USE TEEST;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL DROP TABLE #temp;

CREATE TABLE #temp

(

STORE_ID INT

,PRINTER_NM VARCHAR(50)

,PRIORITY_IND INT

,PRINTER_ID INT

)

INSERT INTO #temp

values(1000,'PRINT1',1,100)

,(1000,'PRINT2',2,101)

,(1001,'PRINT1',1,102)

,(1002,'PRINT2',2,103)

,(1002,'PRINT1',1,104)

,(1002,'PRINT2',2,105)

,(1003,'PRINT1',1,106)

,(1003,'PRINT2',2,107);

SELECT

T.STORE_ID

,MAX(CASE WHEN T.PRIORITY_IND = 1 THEN T.PRINTER_NM END) AS PRIMARYPRINTER_NM

,MAX(CASE WHEN T.PRIORITY_IND = 1 THEN T.PRINTER_ID END) AS PRIMARYPRINTER_ID

,MAX(CASE WHEN T.PRIORITY_IND = 2 THEN T.PRINTER_NM END) AS SECONDARYPRINTER_NM

,MAX(CASE WHEN T.PRIORITY_IND = 2 THEN T.PRINTER_ID END) AS SECONDARYPRINTER_ID

FROM #temp T

GROUP BY T.STORE_ID;

Output

STORE_ID PRIMARYPRINTER_NM PRIMARYPRINTER_ID SECONDARYPRINTER_NM SECONDARYPRINTER_ID

----------- -------------------- ----------------- -------------------- -------------------

1000 PRINT1 100 PRINT2 101

1001 PRINT1 102 NULL NULL

1002 PRINT1 104 PRINT2 105

1003 PRINT1 106 PRINT2 107

ramineni.suresh661 Mr or Mrs. 500 Points: 554 More actions · Answer 2

Thank you very much Eirikur Eiriksson , your Quick suggestion made my day . you deserve a round of applause

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

ramineni.suresh661 (6/2/2016)
Thank you very much Eirikur Eiriksson , your Quick suggestion made my day . you deserve a round of applause

The question now is... do you understand how it works and why? Do you know what the technique is called? If not, please see the following article.

http://qa.sqlservercentral.com/articles/T-SQL/63681/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions