mysql - 如何从数据透视表创建多个表

Question

我有一个生成的数据透视表，如下所示，名称包含列名称，文本包含它们的值，因此需要根据相似的值生成多个表

输入表

ID  Name    text    
1   Name,DOB    John,02/02/1980 
2   FirstName,SSN,City  Ray,987898789,Chicago   
3   Name,DOB    Mary,12/21/1990 
4   FirstName,SSN,City  Cary,987000789,Dallas   
5   PersonID,Code,Zip,Gender,Maritial   1234,A456,23456,M,single    
6   PersonID,Code,Zip,Gender,Maritial   1235,A457,23233,M,single    
7   PersonID,Code,Zip,Gender,Maritial   1236,A458,67675,M,Married   

所以输出表应该是这样的

输出表1

ID  Name    DOB 
1   john    02/02/1980  
3   Mary    02/02/1980  

输出表2

ID  FirstName   SSN City
2   Ray 987898789   Chicago
4   Cary    987000789   Dallas

输出表3

ID  PersonID    Zip Gender  Marital 
5   1234    A456    23456   M   Single  
6   1235    A457    23233   M   Single  
7   1236    A458    67675   M   Married 

有人可以帮我解决这个问题吗？这可以在 Sqlserver、MySQL 或 SSIS 中完成吗？？

Answer 1

我的建议是首先规范您的输入表。在 SQL Server 中，您可以使用递归 CTE 将逗号分隔列表中的数据拆分为行。

CTE 将类似于以下内容:

;with cte (id, col, Name_list, value, text_list) as
(
  select id,
    cast(left(Name, charindex(',',Name+',')-1) as varchar(50)) col,
         stuff(Name, 1, charindex(',',Name+','), '') Name_list,
    cast(left(text, charindex(',',text+',')-1) as varchar(50)) value,
         stuff(text, 1, charindex(',',text+','), '') text_list
  from input
  union all
  select id,
    cast(left(Name_list, charindex(',',Name_list+',')-1) as varchar(50)) col,
    stuff(Name_list, 1, charindex(',',Name_list+','), '') Name_list,
    cast(left(text_list, charindex(',',text_list+',')-1) as varchar(50)) value,
         stuff(text_list, 1, charindex(',',text_list+','), '') text_list
  from cte
  where Name_list > ''
    or text_list > ''
) 
select id, col, value
from cte;

参见SQL Fiddle with Demo 。这将为您提供以下格式的数据:

| ID |       COL |      VALUE |
-------------------------------
|  1 |      Name |       John |
|  2 | FirstName |        Ray |
|  3 |      Name |       Mary |
|  4 | FirstName |       Cary |
|  5 |  PersonID |       1234 |

数据采用该格式后，您就可以根据每个表中所需的列对数据进行透视。

例如，如果您想要 Table1 的数据，您将使用:

;with cte (id, col, Name_list, value, text_list) as
(
  select id,
    cast(left(Name, charindex(',',Name+',')-1) as varchar(50)) col,
         stuff(Name, 1, charindex(',',Name+','), '') Name_list,
    cast(left(text, charindex(',',text+',')-1) as varchar(50)) value,
         stuff(text, 1, charindex(',',text+','), '') text_list
  from input
  union all
  select id,
    cast(left(Name_list, charindex(',',Name_list+',')-1) as varchar(50)) col,
    stuff(Name_list, 1, charindex(',',Name_list+','), '') Name_list,
    cast(left(text_list, charindex(',',text_list+',')-1) as varchar(50)) value,
         stuff(text_list, 1, charindex(',',text_list+','), '') text_list
  from cte
  where Name_list > ''
    or text_list > ''
) 
select *
-- into table1
from
(
  select id, col, value
  from cte
  where col in ('Name', 'DOB')
) d
pivot
(
  max(value)
  for col in (Name, DOB)
) piv;

参见SQL Fiddle with Demo

然后，您将用下一个表的值替换每个查询中的列名称。