0

I am working on SQL Server 2008 R2. I have below 2 tables:

CREATE TABLE TRK_REQUEST(
REQUEST_ID int,
APPROVER_ID int CONSTRAINT app_ID_fk FOREIGN KEY REFERENCES TRK_APPROVER(APPROVER_ID),
APPROVER_LEVEL int)

CREATE TABLE TRK_APPROVER(APPROVER_ID int IDENTITY(1,1) PRIMARY KEY,
NAME Varchar(10))

INSERT INTO TRK_APPROVER VALUES('Approver_1'),('Approver_2'),('Approver_3')

INSERT INTO TRK_REQUEST VALUES(1, 1, 0),(1, 2, 1), (1, 3, 2)

What I want is a version of group_concat() BUT the values should be in separate columns (not comma separated)

I have tried joining the table,

SELECT REQ.REQUEST_ID, APP.NAME FROM TRK_REQUEST REQ
JOIN TRK_APPROVER APP
ON REQ.APPROVER_ID = APP.APPROVER_ID

But it gives me 3 different rows.

The desired output is like this,

| Request_ID  |    APPROVER_NAME1    |    APPROVER_NAME2    |    APPROVER_NAME3
+-------------+----------------------+----------------------+--------------------+
| 1           |    Approver_1        |    Approver_2        |    Approver_3

I found some matching examples here on SO, but not what i expected. Requesting your help.

SQL Fiddle

shA.t
  • 16,580
  • 5
  • 54
  • 111
Touhid K.
  • 351
  • 1
  • 5
  • 23

1 Answers1

1

You could use dynamic crosstab:

DECLARE @sql1   VARCHAR(4000) = ''
DECLARE @sql2   VARCHAR(4000) = ''
DECLARE @sql3   VARCHAR(4000) = ''

SELECT @sql1 = 
'SELECT
    r.REQUEST_ID' + CHAR(10)

SELECT @sql2 = @sql2 +
'   , [' + a.NAME + '] = MAX(CASE WHEN a.APPROVER_ID = ' + CONVERT(VARCHAR(10), a.APPROVER_ID) + ' THEN a.NAME END)' + CHAR(10)
FROM (
    SELECT * FROM TRK_APPROVER
)a
ORDER BY a.APPROVER_ID

SELECT @sql3 = 
'FROM TRK_REQUEST r
INNER JOIN TRK_APPROVER a
    ON a.APPROVER_ID = r.APPROVER_ID
GROUP BY r.REQUEST_ID
ORDER BY r.REQUEST_ID
'

PRINT (@sql1 + @sql2 + @sql3)
EXEC (@sql1 + @sql2 + @sql3)

SQL FIDDLE

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
  • This works as expected. But May I pls request for an a short explanation on how it wokrs? – Touhid K. Mar 16 '15 at 09:16
  • Check the result of `PRINT (@sql1 + @sql2 + @sql3)`, you could see that it's in a form of `MAX(CASE WHEN..END)`, that's what you do in a crosstab. See the article for reference. – Felix Pamittan Mar 17 '15 at 00:13