June 1, 2016 at 9:41 pm
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
June 1, 2016 at 9:50 pm
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
June 2, 2016 at 6:44 am
Thank you very much Eirikur Eiriksson , your Quick suggestion made my day . you deserve a round of applause
June 2, 2016 at 6:56 am
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://www.sqlservercentral.com/articles/T-SQL/63681/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply