单列中的perticluar项的多行数据 [英] Multiple row data for perticluar item in single column
本文介绍了单列中的perticluar项的多行数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我为Goat Data开发了软件。在一份报告中我有山羊数据和她的孩子喜欢：
GoatNumber1 | Child1 | ChildColor | ChildAge
GoatNumber1 | Child2 | ChildColor | ChildAge
GoatNumber1 | Child3 | ChildColor | ChildAge
GoatNumber2 | Child1 | ChildColor | ChildAge
GoatNumber2 | Child2 | ChildColor | ChildAge
我必须这样做：
GoatNumber1 | Child1 | ChildColor | ChildAge | Child1 | ChildColor | ChildAge ...
GoatNumber2 | Child1 | ChildColor | ChildAge | Child2 | ChildColor | ChildAge
这只能通过SQL查询来实现。
每个建议都是赞赏的。
谢谢和问候。
Firdaus Shaikh。
我有什么试过：
我用两个DataGridView实现了它，一个用于SQL数据，一个用于每个山羊的计算输出。大数据的计算部分需要5分钟以上。对于每个过滤器/更改，如出生日期，颜色等，它需要相同的时间。
解决方案
这不是特别容易，因为你只能对一列进行PIVOT。用于针对多列旋转的许多解决方案建议合并信息，例如，获取2016年1月，2016年2月，2016年3月以及基于这些分组的总数，但这种技术在这里不起作用。
以下技术有效，但我相信一定有更好的方法所以我可能会回来！它基于这个博客 SQL Server中PIVOT运算符的危险之美| [ ^ ]
首先我创建了一些示例数据如下：
CREATE TABLE [山羊]（
[id] [ int ] IDENTITY （ 1 ， 1 ） NOT NULL ，
[颜色] [ varchar ]（ 30 ） NULL ，
[DOB] [ date ] NULL ，
[母亲] [ int ] NULL
）< span class =code-keyword> ON [ PRIMARY ]
GO
INSERT INTO 山羊 VALUES
（' Brown& White'，' 2013-01-01'， NULL ）， - First Mother M1
（' Black& Cream'，' 2014-02 -02'， NULL ）， - 第二母亲M2
（' White'，' 2015-03-03'， 1 ）， - M1的第一个孩子
（' Brown& White' ，' 2015-08-08'， 1 ）， - M1的第二个孩子
（' Brown'，' 2016-01-01'， 1 ）， - M1的第3个孩子
（' Black'，< span class =code-string>' 2015-09-09'，2 ）， - M2的第一个孩子
（' Cream'，' 2016-02-02'， 2 ） - - M2的第二个孩子
此查询然后获取您想要的数据：
; cte as
（
选择母亲，身份证，
CAST（ROW_NUMBER（） OVER （ PARTITION BY 母 ORDER BY DOB） AS VARCHAR ） as rn，
Co lor，CAST（DATEDIFF（mm，DOB，GETDATE（）） AS VARCHAR ） as 年龄
FROM 山羊 WHERE 母亲 IS NOT NULL
），cte2 < span class =code-keyword> as
（
SELECT 母亲，id，' Child' + rn as 元素，' Child' + rn as 值
from cte
UNION
SELECT 母亲，id，' 颜色' + rn as Ele颜色 as 值
来自 cte
UNION
SELECT 母亲，id，' 年龄' + rn as 元素，年龄 as 价值
来自 cte
）
SELECT
母亲，
MAX（Child1） As Child1，Max（Color1） As Color1，Max（Age1） As Age1，
MAX（Child2） As Child2，Max（Color2） As Color2，Max（Age2） As Age2，
MAX（Child3）正如 Child3，Max（Color3） As Color3，Max（Age3） As Age3
FROM cte2
PIVOT（MAX（值） FOR 元素 IN （Child1，Color1，Age1，Child2，Color2，Age2，Child3，Color3，Age3）） AS t
GROUP BY 母亲
< u>注意：
1。第一位（cte）为我的数据返回此结果
母亲ID儿童颜色年龄（以月计）
1 3 1白色13
1 4 2棕色和白色8
1 5 3棕色3
2 6 1黑色7
2 7 2奶油2这是您将查询产生当前结果的地方
2。下一位（cte2）正在创建一组键值对，如下所示
母亲身份元素值
1 3年龄1 13
1 3儿童1儿童1
1 3颜色1白色
1 4 Age2 8
1 4 Child2 Child2
1 4 Color2 Brown& White
1 5 Age3 3
1 5 Child3 Child3
1 5 Color3棕色
2 6年龄1 7
2 6儿童1儿童1
2 6颜色1黑色
2 7年龄2 2
2 7儿童2儿童2
2 7颜色2奶油
3。最后一位是使用GROUP和PIVOT生成我们想要的数据并提供这些结果
1 Child1 White 13 Child2 Brown& White 8 Child3 Brown 3
2 Child1 Black 7 Child2 Cream 2 NULL NULL NULL
4。这种方法的明显缺点是假设每只山羊最多只有3个孩子。如果这适合您的模型，那么您不必做任何其他事情，但这是不现实的。为了克服这个限制，你将不得不使用类似的东西来创建动态sql，从（来自母亲的Goat Group中的SELECT count（id）c）选择max（c）作为计数器计算出你需要的列数。如果我有时间，我会回来一个例子
这是一个动态SQL版本，适用于任何数量的孩子
DECLARE @ maxChildren int =（选择 max（c）来自（ SELECT count（id）c 来自山羊组 by 母亲） as counter）
DECLARE @ dynamicMAXbit varchar （max）= ' '
DECLARE @ dynamicCOLbit varchar （max）= < span class =code-string>' '
DECLARE @ loop int = 1
< span class =code-keyword> DECLARE @ loopC varchar （ 3 ）
WHILE @ loop < = @ maxChildren
BEGIN
SET @ loopC = CAST（ @ loop AS varchar ）
SET @ dynamicMAXbit = @ dynamicMAXbit + ' MAX（Child' + @ loopC + ' < span class =code-string>）As Child' + @ loopC
SET @ dynamicMAXbit = @ dynamicMAXbit + ' ，Max（颜色' + @ loopC + ' ）As Color' + @ loopC
SET @ dynamicMAXbit = @ dynamicMAXbit + ' ，Max（年龄' + @ loopC + ' ）As Age' + @ loopC
SET @ dynamicCOLbit = < span class =code-sdkkeyword> @ dynamicCOLbit + ' Child' + @ loopC + ' ，Color' + @ loopC + ' ，年龄' + @ loopC
IF @ loop < @ maxChildren
BEGIN
SET @ dynamicMAXbit = @ dynamicMAXbit + ' ，'
SET @ dynamicCOLbit = @ dynamicCOLbit + ' ，'
END
SET @loop = @ loop + 1
END
DECLARE @sql varchar （max）
SET @sql = ' ; cte为（选择母亲，id，'
SET @ sql = @sql + <跨度类= 代码串> ' <跨度类= 代码串> CAST（ROW_NUMBER（）OVER（PARTITION BY母亲ORDER BY DOB）AS VARCHAR）用作Rn，'
SET @ sql = @ sql + ' 颜色，CAST（DATEDIFF（mm，DOB，GETDATE（））AS VARCHAR）作为年龄从山羊WHERE母亲不是空'
SET @ sql = @sql + ' ），cte2 as（SELECT Mother，id， ''孩子''+作为元素，''孩子''+作为价值'
SET @sql = @sql + ' 来自cte UNION SELECT母，id，''颜色''+作为元素，颜色作为值'
SET @sql = @sql + ' 母亲，身份，''年龄''+作为元素，年龄为价值'
SET @ sql = @ sql + ' 来自cte）SELECT Mother，' + @ dynamicMAXbit + ' FROM cte2'
SET @ sql = @sql + ' PIVOT（MAX（Value）FOR Element IN（' + @ dynamicCOLbit + ' ））AS t GROUP BY Mother'
<跨度类= 代码关键字> EXEC sp_sqlexec <跨度类= 代码sdkkeyword> @ SQL
I have Developed software for Goat Data. In one Report I have Data for Goat and her Childs Like:
GoatNumber1 | Child1 | ChildColor | ChildAge GoatNumber1 | Child2 | ChildColor | ChildAge GoatNumber1 | Child3 | ChildColor | ChildAge GoatNumber2 | Child1 | ChildColor | ChildAge GoatNumber2 | Child2 | ChildColor | ChildAge
I Have to Do it Like:
GoatNumber1 | Child1 | ChildColor | ChildAge | Child1 | ChildColor | ChildAge ... GoatNumber2 | Child1 | ChildColor | ChildAge | Child2 | ChildColor | ChildAge
Can this will be achieved by only SQL Queries.
Every suggestion is Appreciated.
Thanks and Regards.
Firdaus Shaikh.
What I have tried:
I have Implemented it with Two DataGridView, One For SQL Data and One for Calculated Output for each Goat. It takes more than 5 Minutes for Calculation part for Big Data. For each Filter/Changes like Birthdates, Color etc it take Same Time.
解决方案
This is not particularly easy as you can only PIVOT against one column. Many solutions for pivoting against multiple columns suggest "merging" information e.g. get columns Jan-2016, Feb-2016, Mar-2016 and total based on those groupings, but that technique won't work here.
The technique below works, but I'm sure there must be a better way so I may be back! It's based on this blogThe dangerous beauty of the PIVOT operator in SQL Server |[^]
Firstly I created some sample data as follows:
CREATE TABLE [Goat]( [id] [int] IDENTITY(1,1) NOT NULL, [Color] [varchar](30) NULL, [DOB] [date] NULL, [Mother] [int] NULL ) ON [PRIMARY] GO INSERT INTO Goat VALUES ('Brown&White', '2013-01-01',NULL), -- First Mother M1 ('Black&Cream', '2014-02-02',NULL), -- Second Mother M2 ('White', '2015-03-03',1), -- 1st Child of M1 ('Brown&White', '2015-08-08',1), -- 2nd Child of M1 ('Brown', '2016-01-01',1), -- 3rd Child of M1 ('Black', '2015-09-09',2), -- 1st Child of M2 ('Cream', '2016-02-02',2) -- 2nd Child of M2
This query then gets the data you want:
;with cte as ( select Mother, id, CAST(ROW_NUMBER() OVER (PARTITION BY Mother ORDER BY DOB) AS VARCHAR) as rn, Color, CAST(DATEDIFF(mm, DOB, GETDATE()) AS VARCHAR) as Age FROM Goat WHERE Mother IS NOT NULL ), cte2 as ( SELECT Mother, id, 'Child'+rn as Element, 'Child' + rn as Value from cte UNION SELECT Mother, id, 'Color'+rn as Element, Color as Value from cte UNION SELECT Mother, id, 'Age'+rn as Element, Age as Value from cte ) SELECT Mother, MAX(Child1) As Child1, Max(Color1) As Color1, Max(Age1) As Age1, MAX(Child2) As Child2, Max(Color2) As Color2, Max(Age2) As Age2, MAX(Child3) As Child3, Max(Color3) As Color3, Max(Age3) As Age3 FROM cte2 PIVOT(MAX(Value) FOR Element IN (Child1,Color1, Age1, Child2, Color2, Age2, Child3, Color3, Age3)) AS t GROUP BY Mother
NOTES:
1. The first bit (cte) returns this result for my data
Mother Id Child Color Age(in months) 1 3 1 White 13 1 4 2 Brown&White 8 1 5 3 Brown 3 2 6 1 Black 7 2 7 2 Cream 2This is where you would put the query that produces your current results
2. The next bit (cte2) is creating a set of key-value pairs like this
Mother Id Element Value 1 3 Age1 13 1 3 Child1 Child1 1 3 Color1 White 1 4 Age2 8 1 4 Child2 Child2 1 4 Color2 Brown&White 1 5 Age3 3 1 5 Child3 Child3 1 5 Color3 Brown 2 6 Age1 7 2 6 Child1 Child1 2 6 Color1 Black 2 7 Age2 2 2 7 Child2 Child2 2 7 Color2 Cream
3. The last bit is generating the data we want using GROUP and PIVOT and delivers these results
1 Child1 White 13 Child2 Brown&White 8 Child3 Brown 3 2 Child1 Black 7 Child2 Cream 2 NULL NULL NULL
4. The obvious disadvantage to this method is that it assumes each goat only has a maximum of 3 kids. If this fits your model then you don't have to do anything else, but it's unrealistic. To overcome that restriction you are going to have to create dynamic sql using something likeselect max(c) from (SELECT count(id) c from Goat Group by Mother) as counterto work out how many columns you need up front. If I get time I'll come back with a example of that
[EDIT] Here is a dynamic SQL version that will work for any number of kids
DECLARE @maxChildren int = (select max(c) from (SELECT count(id) c from Goat Group by Mother) as counter) DECLARE @dynamicMAXbit varchar(max) = '' DECLARE @dynamicCOLbit varchar(max) = '' DECLARE @loop int = 1 DECLARE @loopC varchar(3) WHILE @loop <= @maxChildren BEGIN SET @loopC = CAST(@loop AS varchar) SET @dynamicMAXbit = @dynamicMAXbit + 'MAX(Child' + @loopC + ') As Child' + @loopC SET @dynamicMAXbit = @dynamicMAXbit + ', Max(Color' + @loopC + ') As Color' + @loopC SET @dynamicMAXbit = @dynamicMAXbit + ', Max(Age' + @loopC + ') As Age' + @loopC SET @dynamicCOLbit = @dynamicCOLbit + 'Child' + @loopC + ',Color' + @loopC + ', Age' + @loopC IF @loop < @maxChildren BEGIN SET @dynamicMAXbit = @dynamicMAXbit + ',' SET @dynamicCOLbit = @dynamicCOLbit + ',' END SET @loop = @loop + 1 END DECLARE @sql varchar(max) SET @sql = ';with cte as (select Mother, id, ' SET @sql = @sql + 'CAST(ROW_NUMBER() OVER (PARTITION BY Mother ORDER BY DOB) AS VARCHAR) as rn,' SET @sql = @sql + 'Color, CAST(DATEDIFF(mm, DOB, GETDATE()) AS VARCHAR) as Age FROM Goat WHERE Mother IS NOT NULL' SET @sql = @sql + '), cte2 as (SELECT Mother, id, ''Child''+rn as Element, ''Child'' + rn as Value ' SET @sql = @sql + 'from cte UNION SELECT Mother, id, ''Color''+rn as Element, Color as Value ' SET @sql = @sql + 'from cte UNION SELECT Mother, id, ''Age''+rn as Element, Age as Value ' SET @sql = @sql + 'from cte ) SELECT Mother,' + @dynamicMAXbit + ' FROM cte2 ' SET @sql = @sql + 'PIVOT(MAX(Value) FOR Element IN (' + @dynamicCOLbit + ')) AS t GROUP BY Mother' EXEC sp_sqlexec @sql
这篇关于单列中的perticluar项的多行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
