13,000,002 members (49,360 online)
Rate this:
See more:
i need a stored procedure which can fetch values from 2 tables. for eg-

table A
```id - name
1  - x
2 -  y```

table B
id- text- value
```1 -  X  -   X1
1  - Y -    Y1
1  - Z -    Z1
2  - A -    A1
2 -  B  -   B1```

now if i fetch value from these two tables where id =1 , then the result should me diplyes like

```id- name- X- Y - Z
1 -  x  - X1- Y1- Z1```

so we need text as a column name and value as a column values.

Posted 15-Nov-12 22:09pm
Updated 15-Nov-12 22:33pm
Nelek125.1K
v2
joshrduncan2012 16-Nov-12 12:37pm

What have you done to try to solve this problem?

Rate this:

## Solution 1

Try something like this:

```DECLARE @cols NVARCHAR(300)
DECLARE @dt NVARCHAR(1000)
DECLARE @pt NVARCHAR(2000)

SET @cols = STUFF((SELECT DISTINCT '],[' + [Name]
FROM A
ORDER BY '],[' + [Name]
FOR XML PATH('')),1,2,'') + ']'

SET @dt = 'SELECT B.[ID], B.[Text], B.[Value] ' +
'FROM A LEFT JOIN B ON A.[ID] = B.[ID] '
--EXEC(@dt)

SET @pt = 'SELECT [ID], ' + @cols + ' ' +
'FROM (' + @dt + ') AS DT ' +
'PIVOT(MAX([Value]) FOR [Text] IN (' + @cols + ')) AS PT ' +
'ORDER BY [ID]'
EXEC(@pt)```

Top Experts
Last 24hrsThis month
 RickZeeland 135 Michael_Davies 40 Jochen Arndt 30 Sergey Alexandrovich Kryukov 25 George Swan 25
 OriginalGriff 4,557 ppolymorphe 2,292 RickZeeland 2,162 Kornfeld Eliyahu Peter 1,973 Richard Deeming 1,593