Creating a view from a set of results

Question

I have several tables all holding small amounts of data on a batch of product. For example, I have a table (called 'Tests') that holds a test number, a test name and the description. This is referenced by my batch table, which holds a test number, test result (as a real) and the batch number itself.

Some batches may have 50 tests, some may have 30, some may have as little as 1.

I was hoping to create a view that converts something like these tables;

BatchNumber  TestNum   TestResult         | TestNumber   TestName    TestDesc
-----------  --------  -----------        | -----------  ---------   ---------
1000         1         1.20               | 1            Thickness    How thick the product is
1001         1         1.30               | 2            Colour       What colour the product is
1001         2         45.1               | 3            Weight       How heavy the product is
...

to the following;

BatchNumber    Thickness    Colour    Weight
------------   ---------    ------    -------
1000           1.20         NULL      NULL
1001           1.30         45.1      NULL
...

Though the 'null' could just be blank, it would probably be better that way, I just used that to better show my requirement.

I've found many articles online on the benefit of PIVOTing, UNPIVOTing, UNIONing but none show the direct benefit, or indeed provide a clear and succinct way of using the data without copying data into a new table, which isn't really useful for my need. I was hoping that a view would be possible so that end-user applications can just call that instead of doing the joins locally.

I hope that makes sense, and thank you!

score 0 · Answer 1 · answered Oct 24 '14 at 08:19

0

CREATE VIEW?

e.g.

 CREATE VIEW myview AS
    SELECT somecolumns from sometable

answered Oct 24 '14 at 08:19

mlinth

2,968
6
30
30

Yeah, but that won't allow me to create a table like the one I described above, it just narrows down what I already have. – Will Whitehead Oct 24 '14 at 08:35
Write a select query - it can be as complicated as you like, using joins, unions - to generate the output you want then stick "CREATE VIEW" above it. – mlinth Oct 24 '14 at 08:38

rjs123431 · Accepted Answer · 2014-10-24T09:07:36.947

You need cross tab query for this.

http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/

DECLARE @Tests Table (TestNumber int, TestName VARCHAR(100),TestDesc varchar(100))
INSERT INTO @Tests
SELECT 1, 'Thickness', '' UNION ALL
SELECT 2, 'Color', '' UNION ALL
SELECT 3, 'Weight', ''

DECLARE @BTests Table (BatchNum int, TestNumber int, TestResult float)
INSERT INTO @BTests
SELECT 1000, 1, 1.20 UNION ALL
SELECT 1001, 1, 1.30 UNION ALL
SELECT 1001, 2, 45.1


;with cte as (
select b.*, t.TestName
from @BTests b
inner join @Tests t on  b.TestNumber = t.TestNumber
)


SELECT Batchnum, [Thickness] AS Thickness, [Color] AS Color, [Weight] as [Weight]
FROM 
(SELECT Batchnum, TestName, testResult
FROM Cte ) ps
PIVOT
(
SUM (testResult)
FOR testname IN
( [Thickness],[Color], [Weight])
) AS pvt

That's great, thank you! Is there a way to, instead of typing the test name, to have the server include those programatically? — Will Whitehead, Oct 24 '14 at 10:02
You need to create dynamic columns for that, see http://stackoverflow.com/questions/12643117/dynamically-create-columns-sql — rjs123431, Oct 25 '14 at 02:07

Creating a view from a set of results

2 Answers2