Solved

Horizontal rows convert into vertical rows - auto

Posted on 2013-11-10
7
553 Views
Last Modified: 2013-11-12
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

Open in new window


Result
Result
Please help.
0
Comment
Question by:Mehram
  • 4
  • 2
7 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39639778
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39640205
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39640681
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
;

Open in new window

see: http://sqlfiddle.com/#!3/17032/14
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Mehram
ID: 39640714
Sir,
New code and screen of result attached. Its removed Column Name.

Result
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

Open in new window

0
 

Author Comment

by:Mehram
ID: 39640723
There is some grouping problem as well since the record should be three thousand some thing while its showing 38000 rows.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39640755
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39640758
Please read the comments in the sql provided, I have repeated one to highlight it
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
;

Open in new window

see: http://sqlfiddle.com/#!3/17032/19
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql Query with datetime 3 24
kill process lock Sql server 9 46
SQL server 2008 and after encryption method 32 45
Please help with the below query - SQL Server 11 17
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question