Fahmieyz Fahmieyz - 4 days ago 5
ASP.NET (C#) Question

Count the 'X' then arrange the value as row in SQL

I have a table in SQL that have many columns which the value of each columns in every row is either ' ' or 'X'. I need to count this 'X' for every columns which can be done by following code;

SELECT COUNT(GVI0) AS GVI0, COUNT(GVI1) AS GVI1, COUNT(GVI2) AS GVI2
FROM dbo.HullInspectionProgram
WHERE (StructureEntry='1' AND Year='2016')


The result of the query is;

GVI0 NDT0 GVI1 NDT1 GVI2 NDT2
11 11 2 4 11 11


However, (in my understanding) in order for this count value to be bind into ASP.net Chart Control with multiple series name 'GVI' and 'NDT', I need to make the column into row for the DataTable.

I try to use UNPIVOT in SQL like this;

SELECT GVI0Count
FROM (
SELECT COUNT(GVI0) AS GVI0, COUNT(GVI1) AS GVI1, COUNT(GVI2) AS GVI2
FROM dbo.HullInspectionProgram
WHERE (StructureEntry='1' AND Year='2016')
)
UNPIVOT (GVI0Count FOR ListOfColumns IN (GVI0)) AS unpivott


but it seem that the code is wrong.

How do I do this?

Answer

I think the following might work for you. At least, as a start.

SELECT *
FROM (
      SELECT COUNT(GVI0) AS GVI0, COUNT(GVI1) AS GVI1, COUNT(GVI2) AS GVI2
      FROM dbo.HullInspectionProgram
      WHERE (StructureEntry='1' AND Year='2016')
     ) P
UNPIVOT (GVI0Count FOR ListOfColumns IN (GVI0, GVI1, GVI2)) AS unpivott
Comments