将表中的列的值分配为另一个表的列 [英] Assign value of a column in a table as columname of another table

问题描述

我有一个父表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 |

这篇关于将表中的列的值分配为另一个表的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！