Databases
StackOverflow2013
Postsdbo
POSQL Syntax: Create a single table with column names created from data stored over multiple tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Syntax: Create a single table with column names created from data stored over multiple tables
    primarykey
    data
    text
    <p>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!). </p> <p>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: </p> <pre><code>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 </code></pre> <p>There are hundreds of parameters in each table (above are just a few) that all references a row from the JobFiles table. </p> <p>When I run the above SELECT statement I get the following result as expected:</p> <pre><code>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 </code></pre> <p>What I am trying to achieve is to arrange the data differently to match how the original 400 column table to look like:</p> <p>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.</p> <p>i.e.</p> <pre><code>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 . . . . . . </code></pre> <p>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. </p> <p>Thank you in advanced.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USBelliez
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Syntax: Create a single table with column names created from data stored over multiple tables
      1. USBelliez
      1. VTFavorite
    2. VO
      singulars
      1. POSQL Syntax: Create a single table with column names created from data stored over multiple tables
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload