pivot sql to create a custom DataTable ??

Question

I am currently working on a project in C# and use MySql as database. I am currently having a problem with creating a custom DataTable I want to show in my Report. Extra Info : A test can have any no combinations of Cells & Valves for a test But for one test it uses set range of Vales and Cells are predefined before each test.

Results Table (Sample)

 TestID | CellID | ValveID | OutputValue1| OutputValue2 |
1       | 1      | 1       | 2.5         | 0.12         |
1       | 1      | 1       | 2.5         | 0.12         |
1       | 1      | 2       | 2.8         | 0.13         |
1       | 2      | 1       | 3.2         | 0.12         |
1       | 2      | 2       | 3.5         | 0.12         |
1       | 2      | 4       | 4.1         | 0.14         |

So for a example Valve 3 is not tested at this particular example. But can use if wanted. Basically my table rows are dynamically need generate based on this results table.

Report Expected

Test ID = 1
                     Valves
       |     1     |     2     |     4     |
CellID |Out1 | Out2| Out1| Out2|Out1 | Out2|
1      | 2.5 | 0.12| 2.8 | 0.13| 2.9 | 0.12|  
2      | 3.2 | 0.12| 3.5 | 0.12| 4.1 | 0.14|

Out1 = OutputValue1 Out2 = OutputValue2 Can any one help me by direct me to a good link or explanation how to get this kind dynamic Data Table

Answer 1

I haven't tested this at all, but I think it's something like this to do it in Sql for the data you provided.

Select s.CellId,
   (select s1.OutputValue1 From Sample s1 Where s1.CellId=s.CellId and s1.ValveId=1 and s1.TestId=s.TestId) as Valve1_Out1,
   (select s1.OutputValue2 From Sample s1 Where s1.CellId=s.CellId and s1.ValveId=1 and s1.TestId=s.TestId) as Valve1_Out2,
   (select s1.OutputValue1 From Sample s1 Where s1.CellId=s.CellId and s1.ValveId=2 and s1.TestId=s.TestId) as Valve2_Out1,
   (select s1.OutputValue2 From Sample s1 Where s1.CellId=s.CellId and s1.ValveId=2 and s1.TestId=s.TestId) as Valve2_Out2,
   (select s1.OutputValue1 From Sample s1 Where s1.CellId=s.CellId and s1.ValveId=4 and s1.TestId=s.TestId) as Valve4_Out1,
   (select s1.OutputValue2 From Sample s1 Where s1.CellId=s.CellId and s1.ValveId=4 and s1.TestId=s.TestId) as Valve4_Out2
 From Sample s
 Where s.TestId=1
 Group By s.CellId, s.TestId
 Order By s.CellId

Can could also manipulate the data on the server in C# once it's loaded into a DataTable. There's an example of something similar here (quick google), which shows how to create DataTables in code. Obviously your requirement is more than a straight pivot, but it's the same principle.

Answer 2

Try this sql code. I have recreated a dummy table resembling your original table. There is a UDF that needs to be created as well. The remaining code can go in as a part of stored proc.

set xact_abort on
begin tran
CREATE TABLE TestTable
(
TestId int not null,
CellId int not null,
valveid int not null,
op1 numeric(5,2) not null,
op2 numeric (5,2) not null
)
go

CREATE FUNCTION GetOutputString
(
@TestId INT,
@CellId INT
)
RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @output VARCHAR(1000)
    SELECT @output = ISNULL(@output + ';','')
                     +
                     CAST(valveid AS VARCHAR(5)) + '#'
                     +
                     CAST(op1 as VARCHAR(10))+ ','
                     +
                     CAST(op2 as VARCHAR(10))
      FROM TestTable
     WHERE TestId = @testid
       AND cellid = @cellid

     RETURN @output
END
go
-- insert dummy data
INSERT INTO TestTable
SELECT 1, 1, 1, 2.5, .12
UNION
SELECT 1, 1, 2, 2.8, .13
UNION
SELECT 1, 2, 1, 3.2, .12
UNION
SELECT 1, 2, 2, 3.5, .12
UNION
SELECT 1, 2, 4, 4.5, .13

-- create temp table/table variable depending on the size of the actual table.
DECLARE @tmp TABLE (
TestId int not null,
CellId int not null,
OutputString VARCHAR(1000) null
)

INSERT INTO @tmp (TestId, CellId)
SELECT TestId, CellId
FROM TestTable
GROUP BY TestId,CellId

UPDATE @tmp
   SET OutputString = dbo.GetOutputString(TestId, CellId)

SELECT * FROM @tmp
rollback

What it does is it returns a semicolon-separated values of output values for each valveid. Of course in the front end, you'll have to write code that identifies valveid as the number between a semicolon (;) and a hash(#). Here is the output that the above SQL produces:

1   1   1#2.50,0.12;2#2.80,0.13
1   2   1#3.20,0.12;2#3.50,0.12;4#4.50,0.13