SQL Server:将行转置为列(n:m关系) [英] SQL server: Transpose Rows to Columns (n:m relationship)
问题描述
我自己尝试了几个小时后，现在需要寻求帮助.到目前为止，我只做过一些基本的SQL.
我想解决以下问题:
(我翻译了几本书供您理解上下文)
我有三个表:
工人(德语中的Mitarbeiter-mitID)
| mitID | Name | FamName | DOB | abtIDref |
|-------|--------|---------|------------|----------|
| 1 | Frank | Sinatra | 12.12.1915 | 1 |
| 2 | Robert | Downey | 4.4.1965 | 2 |
信息:abtIDref是Workplace的1:n关系，但此处未涉及
技能(德语中的Faehigkeiten-faeID)
| faeID | Descr | time | cost |
|-------|-------|------|------|
| 1 | HV | 2 | 0 |
| 2 | PEV | 1 | 0 |
| 3 | Drive | 8 | 250 |
| 4 | Nex | 20 | 1200 |
链接列表
| linkID | mitIDref | feaIDref | when |
|--------|----------|----------|------------|
| 1 | 2 | 1 | 27.07.2014 |
| 2 | 2 | 2 | 01.01.2016 |
| 3 | 2 | 3 | 20.01.2016 |
| 4 | 1 | 3 | 05.06.2015 |
| 5 | 1 | 4 | 02.11.2015 |
所需的结果是:
| mitID | Name | FamName | DOB | abtIDref | HV | PEV | Drive | Nex |
|-------|--------|---------|------------|----------|-----------|------------|------------|------------|
| 1 | Frank | Sinatra | 12.12.1915 | 1 | | | 05.06.2015 | 02.11.2015 |
| 2 | Robert | Downey | 4.4.1965 | 2 | 27.7.2014 | 01.01.2016 | 20.01.2015 | |
或者可以是:
| mitID | Name | FamName | DOB | abtIDref | HV | PEV | Drive | Nex |
|-------|--------|---------|------------|----------|----|-----|-------|-----|
| 1 | Frank | Sinatra | 12.12.1915 | 1 | | | x | x |
| 2 | Robert | Downey | 4.4.1965 | 2 | x | x | x | |
目标是用户/管理员可以添加新技能，并且如果某人具有此技能，则某人可以在此结果列表上看到.
我尝试了什么:
我遇到了多个动态SQL和数据透视函数示例，但是我不知道如何使用它，因为我没有运行像AVG()或MIN()这样的函数. /p>
我这样尝试过:
DECLARE @columns AS VARCHAR(MAX);
DECLARE @sql AS VARCHAR(MAX);
select @columns = substring((Select DISTINCT ',' + QUOTENAME(faeID) FROM mdb_Fähigkeiten FOR XML PATH ('')),2, 1000);
SELECT @sql = 'SELECT * FROM mdb_Mitarbeiter
PIVOT
(
MAX(Value)
FOR mitID IN( ' + @columns + ' )
);';
execute(@sql);
第二种方法是:
declare @collist nvarchar(max)
SET @collist = stuff((select distinct ',' + QUOTENAME(Question)
FROM #t1 -- your table here
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
select @collist
declare @q nvarchar(max)
set @q = '
select *
from (
select
Vorname, Bezeichnung, faeIDref
from (
select #t1.*, #t2.Answer, #t2.parent
from #t1
inner join #t2 on #t1.QID = #t2.QID
) as x
) as source
pivot (
max(Answer)
for Question in (' + @collist + ')
) as pvt
'
exec (@q)
但是TBH我找不到功能. 希望您能为我提供一些指导，以帮助我实现(甚至可以)实现的目标.
我相信下面的查询就是您想要的.根据需要调整列名和表名，以适合您的数据库.
DECLARE @sql AS NVARCHAR(MAX)
DECLARE @cols AS NVARCHAR(MAX)
SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(Descr)
FROM Faehigkeiten ORDER BY faeID
SET @sql = N'
SELECT mitID, Name, FamName, DOB, abtIDref, ' + @cols + '
FROM (
SELECT mitID, Name, FamName, DOB, abtIDref, [when], descr
FROM Mitarbeiter m
JOIN [Link-List] l ON m.mitID = l.mitIDref
JOIN Faehigkeiten f ON f.faeID = l.feaIDref
) a
PIVOT(MAX([when]) FOR descr IN (' + @cols + ')) p'
EXEC sp_executesql @sql
After trying it myself for some hours now I need to ask for help. I only did some basic SQL until now.
I want to solve the following:
(I have translated a couple of things for you to understand the context)
I have three tables:
Workers (Mitarbeiter in German - mitID)
| mitID | Name | FamName | DOB | abtIDref |
|-------|--------|---------|------------|----------|
| 1 | Frank | Sinatra | 12.12.1915 | 1 |
| 2 | Robert | Downey | 4.4.1965 | 2 |
INFO: abtIDref is an 1:n relation for the Workplace, but not involved here
Skills (Faehigkeiten in German - faeID)
| faeID | Descr | time | cost |
|-------|-------|------|------|
| 1 | HV | 2 | 0 |
| 2 | PEV | 1 | 0 |
| 3 | Drive | 8 | 250 |
| 4 | Nex | 20 | 1200 |
Link-List
| linkID | mitIDref | feaIDref | when |
|--------|----------|----------|------------|
| 1 | 2 | 1 | 27.07.2014 |
| 2 | 2 | 2 | 01.01.2016 |
| 3 | 2 | 3 | 20.01.2016 |
| 4 | 1 | 3 | 05.06.2015 |
| 5 | 1 | 4 | 02.11.2015 |
The desired result is:
| mitID | Name | FamName | DOB | abtIDref | HV | PEV | Drive | Nex |
|-------|--------|---------|------------|----------|-----------|------------|------------|------------|
| 1 | Frank | Sinatra | 12.12.1915 | 1 | | | 05.06.2015 | 02.11.2015 |
| 2 | Robert | Downey | 4.4.1965 | 2 | 27.7.2014 | 01.01.2016 | 20.01.2015 | |
Alternative it could be:
| mitID | Name | FamName | DOB | abtIDref | HV | PEV | Drive | Nex |
|-------|--------|---------|------------|----------|----|-----|-------|-----|
| 1 | Frank | Sinatra | 12.12.1915 | 1 | | | x | x |
| 2 | Robert | Downey | 4.4.1965 | 2 | x | x | x | |
The goal is that users/admins can add up new skills and someone can see on this resultlist, if a person has this skill.
What did i try:
I've come across multiple examples of dynamic SQL and the pivot function, but I don't know how to use it in my case, because I don't run a function like AVG() or MIN().
I tried it like this:
DECLARE @columns AS VARCHAR(MAX);
DECLARE @sql AS VARCHAR(MAX);
select @columns = substring((Select DISTINCT ',' + QUOTENAME(faeID) FROM mdb_Fähigkeiten FOR XML PATH ('')),2, 1000);
SELECT @sql = 'SELECT * FROM mdb_Mitarbeiter
PIVOT
(
MAX(Value)
FOR mitID IN( ' + @columns + ' )
);';
execute(@sql);
And a second approach was:
declare @collist nvarchar(max)
SET @collist = stuff((select distinct ',' + QUOTENAME(Question)
FROM #t1 -- your table here
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
select @collist
declare @q nvarchar(max)
set @q = '
select *
from (
select
Vorname, Bezeichnung, faeIDref
from (
select #t1.*, #t2.Answer, #t2.parent
from #t1
inner join #t2 on #t1.QID = #t2.QID
) as x
) as source
pivot (
max(Answer)
for Question in (' + @collist + ')
) as pvt
'
exec (@q)
But TBH I don't get the functions found. I hope you can provide me with some guidance what I have to change (or even if I can) achieve this.
I believe the query below is what you are looking for. Adjust the column and table names as needed to fit your database.
DECLARE @sql AS NVARCHAR(MAX)
DECLARE @cols AS NVARCHAR(MAX)
SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(Descr)
FROM Faehigkeiten ORDER BY faeID
SET @sql = N'
SELECT mitID, Name, FamName, DOB, abtIDref, ' + @cols + '
FROM (
SELECT mitID, Name, FamName, DOB, abtIDref, [when], descr
FROM Mitarbeiter m
JOIN [Link-List] l ON m.mitID = l.mitIDref
JOIN Faehigkeiten f ON f.faeID = l.feaIDref
) a
PIVOT(MAX([when]) FOR descr IN (' + @cols + ')) p'
EXEC sp_executesql @sql
这篇关于SQL Server:将行转置为列(n:m关系)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
