存储过程动态添加列及其值 [英] stored procedure dynamically add column and their values

问题描述

我需要一个可以从2个表中获取值的存储过程.对于例如-

表A

 id-名称
1-x
2-y 

表B
id-文本-值

1 -  X  -   X1
1  - Y -    Y1
1  - Z -    Z1
2  - A -    A1
2 -  B  -   B1

现在，如果我从id = = 1的两个表中获取值，那么结果应该像
一样

 id-名称-X- Y-Z
1-x-X1- Y1- Z1 

因此我们需要使用文本作为列名，使用值作为列值.

[edit]添加了文本块[/edit]

解决方案

尝试如下操作:

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

 SET   @ cols  = STUFF(( SELECT   DISTINCT  ' ]，[' + [姓名]
 FROM  A
订单  BY  '  FOR  XML PATH(' ')) ， 1 ， 2 ，' ')+ ' ]'

 SET   @ dt  = ' 选择B. [ID]，B.[文本]，B.[值]' +
' 在A上从左连接B.[ID] = B. [ID]'
-  EXEC(@dt)

 SET   @ pt  = ' 选择[ID]，' +  @ cols  + ' ' +
'  FROM(' +  @ dt  + ' )AS DT' +
'  PIVOT(MAX([Value])FOR [Text] IN(' + ' '  ORDER BY [ID]'
 EXEC ( @ pt )

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.

[edit]text block added[/edit]

解决方案

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)

这篇关于存储过程动态添加列及其值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！