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.

友情链接