SQL语法：创建具有从存储在多个表中的数据创建的列名的单个表 [英] SQL Syntax: Create a single table with column names created from data stored over multiple tables

问题描述

我已经创建了下面的SQL脚本，创建四个表和插入数据到表中来演示我的问题（我希望这有助于！）。

出现这个问题是因为最初这个数据存储在400列的单个表中，并且它非常难以管理，所以我想找到一个更好的方法来存储参数： / p>

 使用master 
 GO 
创建数据库sptest 
 go 
 
使用sptest 
 go 
 
 CREATE TABLE JobFiles（
 [Id] int PRIMARY KEY IDENTITY（0,1），
 [JobName] nvarchar（256）DEFAULT ，
 [CreateDate] DateTime NOT NULL DEFAULT GETDATE（），
 [ModifyDate] DateTime NOT NULL DEFAULT GETDATE（），
 [CreatedByUser] nvarchar（64）DEFAULT [ModifiedByUser] nvarchar（64）DEFAULT''）
 GO 
 
 
 CREATE TABLE jpChar（
 [jpId] int PRIMARY KEY IDENTITY b $ b [JobId] int REFERENCES JobFiles（Id），
 [jpName] varchar（64），
 [jpValue] nvarchar（255））
 
 
 CREATE TABLE jpInt（
 [jpId] int PRIMARY KEY IDENTITY（0,1），
 [JobId] int REFERENCES JobFiles（Id），
 [jpName] varchar（64），
 [jpValue] int）
 
 CREATE TABLE jpText（
 [jpId] int PRIMARY KEY IDENTITY（0,1），
 [JobId] int REFERENCES JobFiles $ b [jpName] varchar（64），
 [jpValue] Text）
 
 
使用spTest 
 go 
 
 INSERT INTO JobFiles JobName）VALUES（'File0'）
 INSERT INTO JobFiles（JobName）VALUES（'File1'）
 INSERT INTO JobFiles（JobName）VALUES（'File2'）
 
 INSERT INTO jpChar（JobId，jpName，jpValue）VALUES（0，'User'，'Paul'）
 INSERT INTO jpChar（JobId，jpName，jpValue）VALUES（0，'Dept'，'IT'）
 INSERT INTO jpInt（JobId，jpName，jpValue）VALUES（0，'Hours'，'40'）
 INSERT INTO jpText（JobId，jpName，jpValue）VALUES（0，'Notes'，'Some Text'）
 
 
 INSERT INTO jpChar（JobId，jpName，jpValue）VALUES（1，'User'，'Bob'）
 INSERT INTO jpChar（JobId，jpName，jpValue）VALUES ，'Dept'，'Sales'）
 INSERT INTO jpInt（JobId，jpName，jpValue）VALUES（1，'Hours'，'20'）
 INSERT INTO jpText（JobId，jpName，jpValue）VALUES （1，'Notes'，'Some more Text'）
 
 
 INSERT INTO jpChar（JobId，jpName，jpValue）VALUES（2，'User'，'Jane'）
 INSERT INTO jpChar（JobId，jpName，jpValue）VALUES（2，'Dept'，'Support'）
 
 
 SELECT JobFiles.Id，JobFiles.JobName，
 jpChar。 jpName AS cName，jpChar.jpValue AS cValue，
 jpInt.jpName AS iName，jpInt.jpValue AS iValue，
 jpText.jpName AS txtName，jpText.jpValue AS txtValue 
 FROM JobFiles INNER JOIN 
 jpChar ON JobFiles.Id = jpChar.JobId LEFT JOIN 
 jpInt ON JobFiles.Id = jpInt.JobId LEFT JOIN 
 jpText ON JobFiles.Id = jpText.JobId 
  

每个表中有几百个参数（以上只是几个），所有参数都引用了JobFiles表中的一行。

当我运行上面的SELECT语句时，我得到如下结果：

  id JobName cName cValue iName iValue txtName txtValue 
 0 File0用户Paul小时40注释某些文本
 0 File0 Dept IT小时40注释某些文本
 1 File1用户Bob小时20注释有些更多文本
 1 File1 Dept销售时间20注释一些文本
 2 File2用户Jane NULL NULL NULL NULL 
 2 File2部门支持NULL NULL NULL NULL 
  
 
 
 
我想实现的是以不同的方式排列数据，以匹配原始400列表的外观：
 
 
 
将名为cName，iName，txtName的列的数据值返回到列。 
将名为cValue，iValue，txtValue的列的数据值返回到行数据。
 
 
 
 
  id JobName用户部门小时备注
 0 File0 Paul IT 40一些文本
 1 File1 Bob Sales 20一些文本
 2 File2 Jane支持NULL 
。 。 。 。 。 。 
  
我不知道我该怎么做，并会感谢任何建议和帮助？我有其他问题，我将分别张贴。 
 
 
 
感谢您进阶。
解决方案
 Dept记录到自己的表中，并相应地改变你的查询。
 
 
 
你遇到的麻烦是因为'User'/'Bob '和'Dept'/'IT'在当前数据库结构中
 
I have created the SQL script below that creates four tables and inserts data into the tables to demonstrate my question (I hope this helps!).  

This problem arose because originally this data was stored in a single table of 400 columns and it got very unmanageable so I wanted to find a better way to store the parameters: 
use master
GO
create database sptest
go

use sptest
go

CREATE TABLE JobFiles (
            [Id]       int PRIMARY KEY IDENTITY(0,1),
   [JobName]       nvarchar(256)  DEFAULT '',
   [CreateDate]     DateTime NOT NULL DEFAULT GETDATE(),
   [ModifyDate]     DateTime NOT NULL DEFAULT GETDATE(),
   [CreatedByUser]     nvarchar(64)  DEFAULT '',
   [ModifiedByUser]    nvarchar(64)  DEFAULT '')
GO


CREATE TABLE jpChar (
  [jpId] int PRIMARY KEY IDENTITY(0,1),
  [JobId] int REFERENCES JobFiles(Id),
  [jpName] varchar(64),
  [jpValue] nvarchar(255))


CREATE TABLE jpInt (
  [jpId] int PRIMARY KEY IDENTITY(0,1),
  [JobId] int REFERENCES JobFiles(Id),
  [jpName] varchar(64),
  [jpValue] int)

CREATE TABLE jpText (
  [jpId] int PRIMARY KEY IDENTITY(0,1),
  [JobId] int REFERENCES JobFiles(Id),
  [jpName] varchar(64),
  [jpValue] Text)


use spTest
go

    INSERT INTO JobFiles(JobName) VALUES ('File0')
    INSERT INTO JobFiles(JobName) VALUES ('File1')
    INSERT INTO JobFiles(JobName) VALUES ('File2')

    INSERT INTO jpChar(JobId,jpName, jpValue) VALUES (0, 'User', 'Paul')
    INSERT INTO jpChar(JobId,jpName, jpValue) VALUES (0, 'Dept', 'IT')
    INSERT INTO jpInt (JobId,jpName, jpValue) VALUES (0, 'Hours', '40')
    INSERT INTO jpText (JobId,jpName, jpValue) VALUES (0, 'Notes', 'Some Text')


    INSERT INTO jpChar(JobId,jpName, jpValue) VALUES (1, 'User', 'Bob')
    INSERT INTO jpChar(JobId,jpName, jpValue) VALUES (1, 'Dept', 'Sales')
    INSERT INTO jpInt (JobId,jpName, jpValue) VALUES (1, 'Hours', '20')
    INSERT INTO jpText (JobId,jpName, jpValue) VALUES (1, 'Notes', 'Some more Text')


    INSERT INTO jpChar(JobId,jpName, jpValue) VALUES (2, 'User', 'Jane')
    INSERT INTO jpChar(JobId,jpName, jpValue) VALUES (2, 'Dept', 'Support')


SELECT  JobFiles.Id, JobFiles.JobName,   
   jpChar.jpName AS cName, jpChar.jpValue AS cValue,
   jpInt.jpName AS iName, jpInt.jpValue AS iValue,
   jpText.jpName AS txtName, jpText.jpValue AS txtValue
FROM         JobFiles INNER JOIN
                      jpChar ON JobFiles.Id = jpChar.JobId LEFT JOIN
                      jpInt ON JobFiles.Id = jpInt.JobId LEFT JOIN
                      jpText ON JobFiles.Id = jpText.JobId
There are hundreds of parameters in each table (above are just a few) that all references a row from the JobFiles table.  

When I run the above SELECT statement I get the following result as expected:
id  JobName cName   cValue  iName   iValue  txtName txtValue
0 File0 User Paul Hours 40 Notes Some Text
0 File0 Dept IT Hours 40 Notes Some Text
1 File1 User Bob Hours 20 Notes Some more Text
1 File1 Dept Sales Hours 20 Notes Some more Text
2 File2 User Jane NULL NULL NULL NULL
2 File2 Dept Support NULL NULL NULL NULL
What I am trying to achieve is to arrange the data differently to match how the original 400 column table to look like:

Return the data values of the columns named cName, iName, txtName into Columns.
Return the data values of the columns named cValue, iValue, txtValue into row data.

i.e.
id  JobName User   Dept    Hours   Notes
0 File0 Paul   IT      40      Some Text
1 File1 Bob    Sales   20      Some more Text 
2 File2   Jane   Support NULL    NULL
.     .       .       .      .       .
I am not sure how I would go about doing this and would appreciated any advice and help?  I have other questions also which I shall post separately. 

Thank you in advanced.
解决方案
I would probably break out the 'Dept' records into their own table, and alter your query accordingly.

The trouble you're running into is because there's no difference between 'User' / 'Bob' and 'Dept' / 'IT' in your current database structure

                        
这篇关于SQL语法：创建具有从存储在多个表中的数据创建的列名的单个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！