Database

Name     Part     Passed     Finished     Scrap
-----------------------------------------------
John     P-ONE    8          5            7
Jane     P-ONE    4          6            10
Jane     P-TWO    9          2            3
Jack     P-THREE  10         12           6

SELECT UserId,ProductNum,Passed,Finished,Scrapped
FROM
(SELECT UserId, ProductNum, Qty, 'passed' as status
FROM    Passed
UNION
SELECT UserId, ProductNum, Qty, 'finished' as status
FROM   finished
UNION
SELECT UserId, ProductNum, Qty, 'scrapped' as status
FROM   scrapped
) AS SourceTable
PIVOT
(
sum(qty)
FOR status IN ('passed', 'finished','scrapped')
) AS PivotTable;

Select Name, Part, 1 Passed,0 Finished,0 Scrap
from PassedTable
UNION 
Select Name, Part, 0 Passed,1 Finished,0 Scrap
from FinishedTable
UNION
Select Name, Part, 0 Passed,0 Finished,1 Scrap
from ScrapTable

Matt U. wrote:

How is it not normalized

    UserId
    ProductNum
    Status
    Qty

CREATE VIEW Finished AS
 SELECT UserId,ProductNum,Qty
 FROM mytable
 WHERE status = 'finished'

Mycroft Holmes wrote:

Select Name, Part, 1 Qty Passed,0 Finished,0 Scrap
from PassedTable
UNION 
Select Name, Part, 0 Passed,1 Qty Finished,0 Scrap
from FinishedTable
UNION
Select Name, Part, 0 Passed,0 Finished,1 Qty Scrap
from ScrapTable

| Id | Name | Pass   |
----------------------
|  1 | Kate | qwerty |
|  2 | Mike | asd$f5 |

"SELECT Name FROM Users WHERE Id=2"

using System.Data.SqlClient;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
 
namespace SqlExporter
{
class Program
{
static void Main(string[] args)
{
    var server = new Server(new ServerConnection {ConnectionString = new SqlConnectionStringBuilder {DataSource = @"LOCALHOST\SQLEXPRESS", IntegratedSecurity = true}.ToString()});
    server.ConnectionContext.Connect();
    var database = server.Databases["MyDatabase"];
    var output = new StringBuilder();
 
    foreach (Table table in database.Tables)
    {
        var scripter = new Scripter(server) {Options = {ScriptData = true}};
        var script = scripter.EnumScript(new SqlSmoObject[] {table});
        foreach (var line in script)
            output.AppendLine(line);
    }
    File.WriteAllText(@"D:\MyDatabase.sql", output.ToString());
}
}
}

Server.ConnectionContext.ExecuteReader
Server.ConnectionContext.ExecuteWithResults

SELECT 
    'TotalValue' AS Sum_Total_Values_By_City, [London], [Tehran], [Paris]
FROM
  (SELECT City, Value 
   FROM MyTable) AS SourceTable
   PIVOT
   (
     SUM(Value)
     FOR City IN ([London], [Tehran], [Paris])
   ) AS PivotTable

Alex Lush wrote:

Any help or pointers on where to start looking greatly apreciated.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

SELECT order_no, order_line_no, line_type, product, long_description + ' ' + (SELECT B.long_description FROM Table1 B WHERE B.line_type = 'C' AND A.order_no = B.order_no AND A.product = B.product) AS long_description
FROM Table1 A
WHERE line_type = 'P'