将表中的列的值分配为另一个表的列

本文介绍了将表中的列的值分配为另一个表的列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个父表SalType，在该表中Saltype_name是列名.另一个表StaffSal与SalType具有外键关系.在StaffSal中，我为每个SalType_name输入值

I have a parent table SalType and in that table Saltype_name is the column name. Another table StaffSal which has foreign key relationship with SalType. In StaffSal I am entering values for each SalType_name

表结构是这样的

SalType --table1 SS_id SalType_name 1 Basic 2 HRA 3 DA StaffSal --table2 SV_id SS_id SV_value U_id 2 1 15000 11 3 2 0.1 11 4 3 0.75 11 5 1 10000 12 6 2 0.01 12 7 3 0.5 12

我需要这样显示

U_id Basic HRA DA ... 11 15000 .1 .75 12 10000 .01 .5

任何人都可以帮忙吗?

推荐答案

有几种方法可以将数据从行转换为列.

There are several ways that you can transform the data from rows into columns.

SQL Server具有一个PIVOT函数，可用于旋转数据.

SQL Server has a PIVOT function that can be applied to rotate the data.

如果您想将有限数量的值转换为列，则可以对查询进行硬编码:

If you have a finite number of values that you want transformed into columns, then you can hard-code the query:

select u_id, Basic, HRA, DA from ( select t.saltype_name, s.u_id, s.sv_value from saltype t left join staffsal s on t.ss_id = s.ss_id ) src pivot ( max(sv_value) for saltype_name in (Basic, HRA, DA) ) piv;

请参见带演示的SQL提琴.

但是，如果您拥有未知数量的值，那么您将需要实现动态SQL来生成结果:

But if you have an unknown number of values, then you will want to implement dynamic SQL to generate the result:

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(SalType_name) from SalType group by SS_id, SalType_name order by SS_id FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT u_id, ' + @cols + ' from ( select t.saltype_name, s.u_id, s.sv_value from saltype t left join staffsal s on t.ss_id = s.ss_id ) x pivot ( max(sv_value) for saltype_name in (' + @cols + ') ) p ' execute(@query)

请参见带演示的SQL提琴.

两个查询都给出结果:

| U_ID | BASIC | HRA | DA | ------------------------------ | 11 | 15000 | 0.1 | 0.75 | | 12 | 10000 | 0.01 | 0.5 |