如何在stroed过程中动态添加多列？ [英] How to add multiple columns dynamically in stroed procedure?
问题描述
我有下表。
student_id，Student_name，Nw_id，Nw_name这些是固定列。
I have below table.
student_id,Student_name,Nw_id,Nw_name these are fixed columns.
|Student_id |Student_name|Nw_id | Nw_name |
| 1 | add | 23 | cvb |
| 2 | wfg | 45 | eret |
| 2 | wfg | 67 | fdg |
| 2 | wfg | 89 | kkk |
我想在存储过程中按如下方式显示我的表。它应根据学生记录以动态方式创建Nw_id_1，Nw_name_1，...，Nw_id_n，Nw_name_n。
And i want to show my table as follows in stored procedure. It should create Nw_id_1,Nw_name_1,...,Nw_id_n,Nw_name_n dyamically as per the students records.
|Student_id |Student_name|Nw_id | Nw_name |Nw_id_1| Nw_name_1 |Nw_id_2| Nw_name_2 |
| 1 | add | 23 | cvb | NULL | NULL | NULL | NULL |
| 2 | wfg | 45 | eret | 67 | fdg |89 | kkk |
请帮忙。
Please Help.
推荐答案
试试这样，
Try like this,
create table #table1(Student_id int,Student_name nvarchar(max),Nw_id int,Nw_name nvarchar(max))
insert into #table1(Student_id,Student_name,Nw_id,Nw_name) values(1,'add',23,'cvb')
insert into #table1(Student_id,Student_name,Nw_id,Nw_name) values(2,'wfg',45,'eret')
insert into #table1(Student_id,Student_name,Nw_id,Nw_name) values(2,'wfg',67,'fdg')
insert into #table1(Student_id,Student_name,Nw_id,Nw_name) values(2,'wfg',89,'kkk')
select a.*,b.Nw_id,b.Nw_id1,b.Nw_id2 from (
SELECT Student_id,Student_name,[1] as Nw_Name,[2] as Nw_Name1,[3] as Nw_Name2
FROM (
SELECT Student_id,Student_name,Nw_name,ROW_NUMBER() over(partition by student_name order by Nw_id) rn
FROM #table1) up
PIVOT (max(Nw_name) FOR rn IN ([1],[2],[3])) AS pvt) as a inner join (
SELECT Student_id,Student_name,[1] as Nw_id,[2] as Nw_id1,[3] as Nw_id2
FROM (
SELECT Student_id,Student_name,Nw_id,ROW_NUMBER() over(partition by student_name order by Nw_id) rn
FROM #table1) up
PIVOT (max(Nw_id) FOR rn IN ([1],[2],[3])) AS pvt) as b
on a.Student_id=b.Student_id
您可以在这里参考Sql中的pivot：
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot -table-examples / [ ^ ]
作为解决方案1的替代方案，您可能想要考虑如果Student_id有超过3个Nw_id行会发生什么。例如，使用此测试数据...
As an alternative to Solution 1 you might like to consider what would happen if a Student_id had more than 3 Nw_id rows. For example with this test data...
Create table test(
Student_id int,
Student_name varchar(5),
Nw_id int,
Nw_name varchar(5)
)
Insert into test values(1,'add',23,'cvb')
Insert into test values(2,'wfg',45,'eret')
Insert into test values(2,'wfg',67,'fdg')
Insert into test values(2,'wfg',89,'kkk')
Insert into test values(3,'aha',23,'cvb')
Insert into test values(3,'aha',45,'eret')
Insert into test values(3,'aha',67,'fdg')
Insert into test values(3,'aha',89,'kkl')
如果我从解决方案1（或任何替代静态）运行查询pivot）然后我为Student_id = 3得到一个额外的行，但是我没有看到Nw_name ='kkl'的数据！
那是因为我需要知道在进行PIVOT之前我需要多少列 - 但这可能会因桌子上的实际数据而有所不同。
另一种方法是在存储过程中使用动态sql构建动态数据透视表。我也打算将它作为单个PIVOT来做，因为这是我个人的偏好（没有充分的理由），因为它还演示了另一种可以使用的技术。
这里的两个关键点是
a）我想知道每个Student_id的每个Nw_name的行号 - 即对于Student_id = 2，它将是1 = eret，2 = fdg，3 = KKK。我稍后会使用这些信息。我将其命名为 RowN
b）我将把Nw_id和Nw_name列组合在一个<我>固定样式，所以我只需要转动一次。我选择将Nw_id格式化为用零填充的4个字符，然后是Nw_name。因此，Nw_name将始终从结果中的第5个位置开始。我会称之为 combopivot
这个sql会把所有这些都放到一个临时表中。我正在使用临时表，因为我想稍后在
If I run the query from Solution 1 (or any alternative static pivot) then I get an extra row for my Student_id = 3 but I don't see the data for Nw_name = 'kkl'!
That's because I need to know how many columns I'm going to need before I do the PIVOT - but that might vary depending on the actual data on the table.
The alternative is to build up a dynamic pivot using dynamic sql within your stored procedure. I'm also going to do this as a single PIVOT because that is my personal preference (for no good reason) and because it also demonstrates another technique that can be used.
The two key points here are
a) I want to know a "row number" for each Nw_name per Student_id - i.e. For Student_id = 2 it would be 1 = eret, 2 = fdg, 3 = kkk. I'm going to use that information later. I'll call it RowN
b) I'm going to combine the Nw_id and Nw_name columns in a fixed style so that I only need to pivot once. I've chosen to format the Nw_id into 4 characters padded with zeroes, followed by the Nw_name. Thus the Nw_name will always start in the 5th position in the results. I'll call it combopivot
This sql will put all of that into a temporary table. I'm using a temporary table because I want to use the information several times later on
SELECT Student_id, Student_name, CAST(REPLICATE('0',4-LEN(Nw_id)) AS varchar(4)) + CAST(Nw_id AS varchar(4)) + Nw_Name as combopivot
,'R' + CAST(ROW_NUMBER() OVER
(PARTITION BY Student_id ORDER BY Student_name ASC) AS Varchar(3)) AS RowN
into #temp
FROM test;如果我查询它，将返回
Student_id Student_name combopivot RowN
1 add 0023cvb R1
2 wfg 0045eret R1
2 wfg 0067fdg R2
2 wfg 0089kkk R3
3 aha 0023cvb R1
3 aha 0045eret R2
3 aha 0089kkl R3
3 aha 0067fdg R4
现在我可以处理该PIVOT的未知列数...我正在使用该临时表并拿起 RowN 列
Now I can deal with the unknown number of columns for that PIVOT...I'm using that temporary table and picking up the RowN column
DECLARE @cols varchar(MAX)
SELECT @cols = STUFF((SELECT ',' + [RowN]
FROM (SELECT DISTINCT RowN FROM #temp) T
ORDER BY [RowN]
FOR XML PATH('')), 1, 1, '')使用 STUFF 是一种创建逗号分隔列表的简单方法在T-SQL中。对于我的测试数据，它产生一个varchar包含
Using STUFF is a nice easy way of creating a comma-separated list in T-SQL. For my test data it produces a varchar containing
R1,R2,R3,R4
现在我需要照顾 combopivot ...我合并了数据，所以我可以只有一个数据透视，但格式不符合要求，所以我需要拆分该列进入它的id +名称我的结果集中的原始列。返回临时表...
Now I need to take care of combopivot... I combined the data so I could just have the one pivot but the format isn't what the requirements stated, so I need to split that column into it's id + name original columns in my result set. Back to that temporary table...
DECLARE @selects varchar(MAX)
SELECT @selects = STUFF( (SELECT ',' + 'SUBSTRING(' + [RowN] + ',1,4) AS ID' + [RowN] + ',SUBSTRING(' + [RowN] + ',5,len(' + [RowN] +')) AS NAME' + [RowN]
FROM (SELECT DISTINCT RowN FROM #temp) T
ORDER BY [RowN]
FOR XML PATH('')),1,1,'')这将给出
SUBSTRING(R1,1,4) AS IDR1,SUBSTRING(R1,5,len(R1)) AS NAMER1,
SUBSTRING(R2,1,4) AS IDR2,SUBSTRING(R2,5,len(R2)) AS NAMER2,
SUBSTRING(R3,1,4) AS IDR3,SUBSTRING(R3,5,len(R3)) AS NAMER3,
SUBSTRING(R4,1,4) AS IDR4,SUBSTRING(R4,5,len(R4)) AS NAMER4我插入一些换行只是为了让它更清晰 - 在最终查询中不需要它们。
现在我们可以将它们组合成一个查询字符串并执行它
I inserted some linefeeds just to make it clearer - they are not required in the final query.
Now we can put it all together into a single query string and execute it
DECLARE @sql VARCHAR(MAX)
SELECT @sql = 'SELECT Student_id, Student_name, ' + @selects +
' FROM (SELECT * FROM #temp) as s
PIVOT
(
MAX(combopivot)
FOR [RowN] IN (' + @cols + ') ) AS P
ORDER BY 1'
EXEC sp_executesql @sql将返回
S_id S_name IDR1 NameR1 IDR2 NameR2 IDR3 NameR3 IDR4 NameR4
1 add 0023 cvb (null) (null) (null) (null) (null) (null)
2 wfg 0045 eret 0067 fdg 0089 kkk (null) (null)
3 aha 0023 cvb 0045 eret 0089 kkl 0067 fdg
我'我承认它起初看起来有点尴尬，但它会满足我所描述的方式出现的其他数据。
所有这些都针对SQL Server 2008所以可能会有进一步的改进更高版本。
为了完整性，这里是存储过程的主体，单击
I'll admit it looks a bit awkward at first, but it will cater for additional data appearing in the way I described.
All of this has targeted SQL Server 2008 so there may be further improvements to be had with later versions.
For completeness here is the main body of the stored procedure in a single hit
SELECT Student_id, Student_name, CAST(REPLICATE('0',4-LEN(Nw_id)) AS varchar(4)) + CAST(Nw_id AS varchar(4)) + Nw_Name as combopivot
,'R' + CAST(ROW_NUMBER() OVER
(PARTITION BY Student_id ORDER BY Student_name ASC) AS Varchar(3)) AS RowN
into #temp
FROM test;
DECLARE @cols varchar(MAX)
SELECT @cols = STUFF((SELECT ',' + [RowN]
FROM (SELECT DISTINCT RowN FROM #temp) T
ORDER BY [RowN]
FOR XML PATH('')), 1, 1, '')
DECLARE @selects varchar(MAX)
SELECT @selects = STUFF( (SELECT ',' + 'SUBSTRING(' + [RowN] + ',1,4) AS ID' + [RowN] + ',SUBSTRING(' + [RowN] + ',5,len(' + [RowN] +')) AS NAME' + [RowN]
FROM (SELECT DISTINCT RowN FROM #temp) T
ORDER BY [RowN]
FOR XML PATH('')),1,1,'')
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = 'SELECT Student_id, Student_name, ' + @selects +
' FROM (SELECT * FROM #temp) as s
PIVOT
(
MAX(combopivot)
FOR [RowN] IN (' + @cols + ') ) AS P
ORDER BY 1'
EXEC sp_executesql @sql
这篇关于如何在stroed过程中动态添加多列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
