SQL Syntax: Create a single table with column names created from data stored over multiple tables

Question

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.

Answer 1

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

Answer 2

It seems that instead of normalizing your design (1NF, 2NF, 3NF) you have created tables based on data type of each column, and hence pivoted tables with your script.

You can not simply join as if you had logical entities, but have to pivot the whole thing back. Here are some pivoting examples.

I hope you still have the original. I would suggest to normalize your design using entities and relationships, instead of this approach.