Dynamic rows into columns

Question

Hi I need to be able to join two tables and return the second table as columns to the first table. I need to create (dependent first name, dependent last name, and dependent relationship) based on the max number depid (which can be dynamic).

thank you in advance

table 1

+-------------+-------------+------------+
| employeeid  | first name  | last name  |
+-------------+-------------+------------+
|           1 | bill        | johnson    |
|           2 | matt        | smith      |
|           3 | katy        | lewis      |
+-------------+-------------+------------+

table 2

+-------------------------------------------------------------------+
| employeeid |dependent id  | First Name | Last Name | Relationship |
+-------------------------------------------------------------------+
| 1               1            mary          johnson    spouse      |
| 1               2            aaron         johnson     child      |
| 2               1            eric          smith       child      |
+-------------------------------------------------------------------+

expected output

+------------+------------+-----------+----------------------+---------------------+------------------------+----------------------+---------------------+------------------------+
| employeeid | first name | last name | dependent first name | dependent last name | dependent relationship | dependent first name | dependent last name | dependent relationship |
+------------+------------+-----------+----------------------+---------------------+------------------------+----------------------+---------------------+------------------------+
|          1 | bill       | johnson   | mary                 | johnson             | spouse                 | aaron                | johnson             | child                  |
|          2 | matt       | smith     | eric                 | smith               | child                  |                      |                     |                        |
|          3 | katty      | lewis     |                      |                     |                        |                      |                     |                        |
+------------+------------+-----------+----------------------+---------------------+------------------------+----------------------+---------------------+------------------------+

Consider handling issues of data display at the presentation layer/application-level code (e.g. a simple PHP loop acting on an ordered array) — Strawberry, Mar 24 '14 at 14:28
The desired result is utterly impossible to achieve with SQL alone, because the column count of your SQL query has to be fixed; it has to be known before looking at the data. — Alexander, Mar 24 '14 at 14:58
Are you looking for something similar to this? [SQLFiddle](http://sqlfiddle.com/#!3/aae10/8) — Mark C., Mar 24 '14 at 15:06
@Newbie: I suspect not. OP want multiple sets of dependents in the same row--an arbitrarily wide result set instead. That what is shown in the "expected output." But I concur with previous comments that this isn't likely to be possible in MySQL and it should be done in presentation layer. — Karl Kieninger, Mar 24 '14 at 15:32
possible duplicate of [How to pivot a MySQL entity-attribute-value schema](http://stackoverflow.com/questions/649802/how-to-pivot-a-mysql-entity-attribute-value-schema) — Vatev, Mar 24 '14 at 16:09

score 0 · Accepted Answer · answered Mar 24 '14 at 21:58

You Can do This with dynamic SQL & XML Path example SQL below

--Table 1

CREATE TABLE #TMP1 (EMP_ID INT, NAME Char(10) ) INSERT INTO #TMP1 VALUES (1,'One') INSERT INTO #TMP1 VALUES (2,'TWO') INSERT INTO #TMP1 VALUES (3,'Three')

--Table 2

CREATE TABLE #TMP2 (EMP_ID INT, DP_ID INT,FNAME Char(10),Rel Char(10) ) INSERT INTO #TMP2 VALUES (1,1,'Spouse One','Spouse') INSERT INTO #TMP2 VALUES (1,2,'Child One','Child') INSERT INTO #TMP2 VALUES (2,1,'Child TWO','Child')

Declare @CNT Int , @Ctr int = 0 , @SQL VarChar(MAX)

--Get Max Dependent ID

SELECT @CNT = MAX(DP_ID) from #TMP2

--For Verification SELECT @CNT

--Build Dynamic SQL to get the dataset

SET @SQL = 'SELECT Emp_ID '

While @Ctr < @CNT

Begin

Set @Ctr = @Ctr+1
SET @SQL = @SQL + ', ( SELECT FName+'+''''+''''+'  FROM #TMP2 Where #TMP1.EMP_ID = #TMP2.EMP_ID and #TMP2.DP_ID = '+Convert(VarChar(2),@Ctr)+' For XML Path ('+''''+''''+') ) as FName'+Convert(VarChar(2),@Ctr)
SET @SQL = @SQL + ',  ( SELECT Rel+'+''''+''''+'   FROM #TMP2 Where #TMP1.EMP_ID = #TMP2.EMP_ID and #TMP2.DP_ID = '+Convert(VarChar(2),@Ctr)+' FOR XML Path ('+''''+''''+') )  as Rel'+Convert(VarChar(2),@Ctr)

End

SET @SQL = @SQL+' FROM #TMP1 '

--For Verification Print the Dynamic SQL

Select @SQL

--Execute the dynamic SQL

EXEC(@SQL)

Dynamic rows into columns

1 Answers1