Please help me to write the query logic

Transact-SQL
1

I have two tables #Service and #Cross.I would like to convert the multiple rows in #Service into single row based on the value available in #Cross.

create table #Service
(
id int,
code char(2)
);

create table #Cross
(
code varchar(2),
switch varchar(10)
);

create table #ServiceMap
(
id int,
switch1 char(1),
switch2 char(1),
switch3 char(1),
switch4 char(1)

);

insert into #Service
select 1001,'50' union
select 1001,'60' union
select 1001,'70' union
select 1002,'60' union
select 1003,'50' union
select 1003,'70' union
select 1004,'80'

insert into #Cross
select '50','switch1' union
select '60','switch2' union
select '70','switch3' union
select '70','switch4'

insert into #ServiceMap
select 1001,'Y','Y','Y','N' union
select 1002,'N','Y','N','N' union
select 1003,'Y','N','Y','N' union
select 1004,'N','N','N','Y'

Please help me to write a query to get result in #ServiceMap like below.

id switch1 switch2 switch3 switch4

1001 Y Y Y N
1002 N Y N N
1003 Y N Y N
1004 N N N Y
(4 rows)Execution time: 0.008 seconds

Thanks,
Binto

2 
SELECT 
    S.id,
    MAX(CASE WHEN C.cross_num = 1 THEN 'Y' ELSE 'N' END) AS switch1,
    MAX(CASE WHEN C.cross_num = 2 THEN 'Y' ELSE 'N' END) AS switch2,
    MAX(CASE WHEN C.cross_num = 3 THEN 'Y' ELSE 'N' END) AS switch3,
    MAX(CASE WHEN C.cross_num = 4 THEN 'Y' ELSE 'N' END) AS switch4
FROM #Service S
LEFT OUTER JOIN (
    SELECT *, ROW_NUMBER() OVER(ORDER BY Code) AS cross_num
    FROM #Cross
) AS C ON S.code = C.code
GROUP BY S.id
ORDER BY S.id
2 Likes
3

Or, if you want to have the values themselves as the column headers, use code below. Result set from this code is:

id 50 60 70 80
1001 Y Y Y N
1002 N Y N N
1003 Y N Y N
1004 N N N Y 
DECLARE @sql varchar(max)

IF OBJECT_ID('tempdb.dbo.#ServiceMap') IS NOT NULL
    DROP TABLE #ServiceMap

SELECT 
    S.id,
    MAX(CASE WHEN C.cross_num = 1 THEN 'Y' ELSE 'N' END) AS switch1,
    MAX(CASE WHEN C.cross_num = 2 THEN 'Y' ELSE 'N' END) AS switch2,
    MAX(CASE WHEN C.cross_num = 3 THEN 'Y' ELSE 'N' END) AS switch3,
    MAX(CASE WHEN C.cross_num = 4 THEN 'Y' ELSE 'N' END) AS switch4
INTO #ServiceMap
FROM #Service S
LEFT OUTER JOIN (
    SELECT *, ROW_NUMBER() OVER(ORDER BY Code) AS cross_num
    FROM #Cross
) AS C ON S.code = C.code
GROUP BY S.id
ORDER BY S.id

SELECT @sql = CAST((
    SELECT 'EXEC tempdb.sys.sp_rename ''#ServiceMap.switch' +
        CAST(cross_num AS varchar(5)) + ''', ''' + C.Code + ''', ''COLUMN''; '
    FROM (
        SELECT *, ROW_NUMBER() OVER(ORDER BY Code) AS cross_num
        FROM #Cross
    ) AS C
    FOR XML PATH(''), TYPE
    ) AS varchar(max))
/*PRINT @sql*/
EXEC(@sql)

SELECT * 
FROM #ServiceMap SM
ORDER BY SM.id
2 Likes
4

Hi ScottPletcher,

Thank you so much.

My solution was for Sybase.So I cannot use 'ROW_NUMBER'.
I modified the query as below.

SELECT
S.id,
MAX(CASE WHEN C.code = '50' THEN 'Y' ELSE 'N' END) AS switch1,
MAX(CASE WHEN C.code = '60' THEN 'Y' ELSE 'N' END) AS switch2,
MAX(CASE WHEN C.code = '70' THEN 'Y' ELSE 'N' END) AS switch3,
MAX(CASE WHEN C.code = '80' THEN 'Y' ELSE 'N' END) AS switch4
FROM (
SELECT *
FROM #Cross
) AS C
LEFT OUTER JOIN #Service S ON S.code = C.code
GROUP BY S.id
ORDER BY S.id

It worked for me.

Thanks again.

5

Ooh, sorry, I don't Sybase syntax, just SQL Server. Glad it still helped.

1 Like