# Horizontal rows convert into vertical rows - auto

Dear Experts,
I am using following query to get result of horizontal rows into vertical rows. this is working perfect only need to convert null values into zero (0) and display record order by TransNo, ExamCode,SC

``````Declare @Cyear Varchar(100)
Set @Cyear='1314'

DECLARE @cols NVARCHAR(2000)
DECLARE @query NVARCHAR(4000)

/* replace this with real query */
Drop Table #Result
Create Table #Result (Transno Varchar(200), ExamCode Varchar(100), SC Varchar(100), SN Varchar(100), ObtMarks Numeric(15,2), d int)
Insert Into #Result(Transno, ExamCode, SC, SN, ObtMarks,d)
SELECT
a.Transno
, ExamCode = a.examcode + cj.suffix
, a.Sc
, b.Sn
, ObtMarks=case when cj.d =1 then a.Om else a.Om1 end
, cj.d
FROM result_det a
JOIN Subject b
ON a.sc = b.subjectid
JOIN result_mst c
ON a.transno = c.transno
JOIN Examduration d
ON a.examcode = d.examcode
CROSS JOIN ( SELECT '-1' AS suffix, 1 as d UNION ALL
SELECT '-2' AS suffix, 2 as d
) AS cj
WHERE c.Cyear = @Cyear
--Order by SC, Transno,ExamCode

--select a.Transno, a.examcode, a.Sc, a.Sn, ObtMarks, d from result_det a
--SELECT Transno, ExamCode,SN , ObtMarks FROM #Result Order by SC, Transno,ExamCode

SELECT  @cols = STUFF(
(
SELECT DISTINCT ',' + quotename(SN)
FROM  #Result
--ORDER BY ',' + quotename(SC) /* optionally, sort this */
FOR XML PATH('')
)
, 1, 1, '')

--SELECT  @cols

SET @query = N'SELECT Transno, ExamCode,'
+ @cols
+ ' FROM (SELECT Transno, ExamCode,SN , ObtMarks FROM #Result) AS p PIVOT (MAX([ObtMarks]) FOR SN IN ( '
+ @cols
+ ' )) AS pvt;'

--SELECT @query
EXECUTE(@query)

--Drop Table #Result
``````

Result

###### Who is Participating?

freelancerCommented:
``````DECLARE @slct  NVARCHAR(4000)
DECLARE @cols  NVARCHAR(4000)
DECLARE @query NVARCHAR(4000)

/* replace this with real query */
/* replace this with real query */
/* replace this with real query */
/* replace this with real query */
/* replace this with real query */
/* replace this with real query */
/* replace this with real query */
/* replace this with real query */
/* replace this with real query */
/* replace this with real query */
/* replace this with real query */
/* replace this with real query */
/* replace this with real query */
--Drop Table #Result
Create Table #Result (Transno Varchar(200), ExamCode Varchar(100), SC Varchar(100), SN Varchar(100), ObtMarks Numeric(15,2), d int)
Insert Into #Result(Transno, ExamCode, SC, SN, ObtMarks,d)
select
a.Transno
, ExamCode = a.examcode + cj.suffix
, a.Sc
, a.Sn
, ObtMarks = case when cj.d =1 then a.Om else a.Om1 end
, cj.d
from result_det a
CROSS JOIN ( SELECT '-1' AS suffix, 1 as d UNION ALL
SELECT '-2' AS suffix, 2 as d
) AS cj

SELECT  @cols = STUFF(
(
SELECT DISTINCT ',' + quotename(SN)
FROM  #Result
FOR XML PATH('')
)
, 1, 1, '')
--SELECT  @cols

SELECT  @slct = STUFF(
(
SELECT DISTINCT ', ISNULL(' + quotename(SN) + ',0) as ' + quotename(SN)
FROM  #Result
ORDER BY ', ISNULL(' + quotename(SN) + ',0) as ' + quotename(SN)
FOR XML PATH('')
)
, 1, 1, '')
--SELECT  @slct

SET @query = N'SELECT Transno, ExamCode,'
+ @slct
+ ' FROM (SELECT Transno,ExamCode,SC,SN,ObtMarks FROM #Result) AS p PIVOT ( MAX([ObtMarks]) FOR SN IN ( '
+ @cols
+ ' )) AS pvt ORDER BY Transno, ExamCode,SC;'

--SELECT @query
EXECUTE(@query)

Drop Table #Result
;
``````
see: http://sqlfiddle.com/#!3/17032/19
0

Commented:
Use:

SELECT... ISNULL(col_numeric,0) as col_numeric ...

But Make sure the type of the column is a numeric one like, tinyint, short, int, float, money, decimal, numeric or else will fail.

If you have columns that are string types they you have to use:

SELECT... ISNULL(col_string,'') as col_string ...

If you have datetime columns you will have to convert them to varchar first:

SELECT... ISNULL(CONVERT(varchar(25), col_datetime, 121),'') as col_datetime ...
0

freelancerCommented:
line 39
remove the -- judging from that sample output you may want to order the columns

line 17
from
, ObtMarks=case when cj.d =1 then a.Om else a.Om1 end
to
, ObtMarks=case when cj.d =1 then isnull(a.Om,0) else isnull(a.Om1,0) end

To include SC and the order by you just adjust the concatenation of @query
i.e that concatenation is a query

, the following is untested but should work I think.

SET @query = N'SELECT Transno, ExamCode,'
+ @cols
+ ' FROM (SELECT Transno, ExamCode, SC, SN , ObtMarks FROM #Result) AS p PIVOT (MAX([ObtMarks]) FOR SN IN ( '
+ @cols
+ ' )) AS pvt ORDER BY TransNo, ExamCode, SC;'

if not can you post some sample data from #result
0

freelancerCommented:
Just noticed that my approach to those nulls won't work - sorry. You need to include ISNULL(...) into the final select clause (inside @query). So it's more like this:
``````DECLARE @cols NVARCHAR(2000)
DECLARE @query NVARCHAR(4000)

/* replace this with real query */
--Drop Table #Result
Create Table #Result (Transno Varchar(200), ExamCode Varchar(100), SC Varchar(100), SN Varchar(100), ObtMarks Numeric(15,2), d int)
Insert Into #Result(Transno, ExamCode, SC, SN, ObtMarks,d)
select
a.Transno
, ExamCode = a.examcode + cj.suffix
, a.Sc
, a.Sn
, ObtMarks = case when cj.d =1 then a.Om else a.Om1 end
, cj.d
from result_det a
CROSS JOIN ( SELECT '-1' AS suffix, 1 as d UNION ALL
SELECT '-2' AS suffix, 2 as d
) AS cj

SELECT  @cols = STUFF(
(
SELECT DISTINCT ',' + quotename(SN)
FROM  #Result
ORDER BY ',' + quotename(SN) /* optionally, sort this */
FOR XML PATH('')
)
, 1, 1, '')
--SELECT  @cols

SET @query = N'SELECT Transno, ExamCode,'
+ replace( replace(@cols,'[','isnull([') ,']','],0)')
+ ' FROM (SELECT Transno,ExamCode,SC,SN,ObtMarks FROM #Result) AS p PIVOT ( MAX([ObtMarks]) FOR SN IN ( '
+ @cols
+ ' )) AS pvt ORDER BY Transno, ExamCode,SC;'

--SELECT @query
EXECUTE(@query)

Drop Table #Result
;
``````
see: http://sqlfiddle.com/#!3/17032/14
0

Author Commented:
Sir,
New code and screen of result attached. Its removed Column Name.

Query
``````DECLARE @cols NVARCHAR(2000)
DECLARE @query NVARCHAR(4000)

/* replace this with real query */
--Drop Table #Result
Create Table #Result (Transno Varchar(200), ExamCode Varchar(100), SC Varchar(100), SN Varchar(100), ObtMarks Numeric(15,2), d int)
Insert Into #Result(Transno, ExamCode, SC, SN, ObtMarks,d)
select
a.Transno
, ExamCode = a.examcode + cj.suffix
, a.Sc
, a.Sn
, ObtMarks=case when cj.d =1 then a.Om else a.Om1 end
, cj.d
from result_det a
CROSS JOIN ( SELECT '-1' AS suffix, 1 as d UNION ALL
SELECT '-2' AS suffix, 2 as d
) AS cj

SELECT  @cols = STUFF(
(
SELECT DISTINCT ',' + quotename(SN)
FROM  #Result
ORDER BY ',' + quotename(SN) /* optionally, sort this */
FOR XML PATH('')
)
, 1, 1, '')
--SELECT  @cols

SET @query = N'SELECT Transno, ExamCode,'
+ replace( replace(@cols,'[','isnull([') ,']','],0)')
+ ' FROM (SELECT Transno,ExamCode,SC,SN,ObtMarks FROM #Result) AS p PIVOT ( MAX([ObtMarks]) FOR SN IN ( '
+ @cols
+ ' )) AS pvt ORDER BY Transno, ExamCode,SC;'

--SELECT @query
EXECUTE(@query)

Drop Table #Result
``````
0

Author Commented:
There is some grouping problem as well since the record should be three thousand some thing while its showing 38000 rows.
0

freelancerCommented:
Mehram

there is a subtle distinction between asking a question and expecting someone to develop your code.

The query you use to generate the #Result is yours, all I did was save time by avoiding the union all - if what I included here has expanded the rows - that is because it's an example only, e.g. I don't have all your tables shown in that query

Please replace how you generate #Results with the correct one. I expect you can do this - if not tell us.

Regarding the headings, yes that is a side effect of fixing the nulls. To solve this requires a bit more effort, so I'll get back to on this point.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.