Rows into columns. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Rows into columns.

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-11-10 : 10:26:53

Dear All,

I am using following query for getting the count. But I am getting following output in different rows. I want the desired output in a single query. Can anybody please tell me how to do this(Desired Output) in one query ?

Output:

SName RName Count
iNET iNET_Plan101PremiumL 270228
iNET iNET_Plan101PremiumL 270228

Desired Output:

SName RName CType Count
iNET iNET_Plan101PremiumL Interstate 270228
iNET iNET_Plan101PremiumL Intrastate 270228

Query:

DECLARE @rate_sheet_Id VARCHAR(36);
SET @rate_sheet_Id='918FE712-ED7F-43A1-8F9B-4A6A39121DDB';

select cr.Name,rs.Name,Count(*) As Interstate from tb_lcr_rate_sheet rs (nolock)
inner join tb_lcr_carrier cr (nolock) on rs.Carrier_ID = cr.ID
inner join tb_lcr_work_sheet ws (nolock) on rs.ID = ws.Rate_Sheet_ID
inner join tb_lcr_normalized_rate_sheet nrs (nolock) on ws.ID = nrs.Work_Sheet_ID
where rs.ID = @rate_sheet_Id and nrs.Call_Type_ID = 'INTERSTATE'
group by cr.Name,rs.Name

select cr.Name,rs.Name,Count(*) As Intrastate from tb_lcr_rate_sheet rs (nolock)
inner join tb_lcr_carrier cr (nolock) on rs.Carrier_ID = cr.ID
inner join tb_lcr_work_sheet ws (nolock) on rs.ID = ws.Rate_Sheet_ID
inner join tb_lcr_normalized_rate_sheet nrs (nolock) on ws.ID = nrs.Work_Sheet_ID
where rs.ID = @rate_sheet_Id and nrs.Call_Type_ID = 'INTRASTATE'
group by cr.Name,rs.Name

Thanks and Regard's
Harish Patil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 10:47:21

[code]
DECLARE @rate_sheet_Id VARCHAR(36);
SET @rate_sheet_Id='918FE712-ED7F-43A1-8F9B-4A6A39121DDB';

select cr.Name,rs.Name,Count(*) As Interstate from tb_lcr_rate_sheet rs (nolock)
inner join tb_lcr_carrier cr (nolock) on rs.Carrier_ID = cr.ID
inner join tb_lcr_work_sheet ws (nolock) on rs.ID = ws.Rate_Sheet_ID
inner join tb_lcr_normalized_rate_sheet nrs (nolock) on ws.ID = nrs.Work_Sheet_ID
where rs.ID = @rate_sheet_Id and nrs.Call_Type_ID = 'INTERSTATE'
group by cr.Name,rs.Name
union all
select cr.Name,rs.Name,Count(*) As Intrastate from tb_lcr_rate_sheet rs (nolock)
inner join tb_lcr_carrier cr (nolock) on rs.Carrier_ID = cr.ID
inner join tb_lcr_work_sheet ws (nolock) on rs.ID = ws.Rate_Sheet_ID
inner join tb_lcr_normalized_rate_sheet nrs (nolock) on ws.ID = nrs.Work_Sheet_ID
where rs.ID = @rate_sheet_Id and nrs.Call_Type_ID = 'INTRASTATE'
group by cr.Name,rs.Name
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-11-10 : 10:56:03

Hello Friend,

Thnks. In same query I want to show the count as per the interstate and intrastate. Means I want to add column(CType) which shows the intersate and intrastate count. Please see the following output. I want this.

SName RName CType Count
iNET iNET_Plan101PremiumL Interstate 270228
iNET iNET_Plan101PremiumL Intrastate 270228

Thanks and Regard's
Harish Patil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 11:00:14

[code]DECLARE @rate_sheet_Id VARCHAR(36);
SET @rate_sheet_Id='918FE712-ED7F-43A1-8F9B-4A6A39121DDB';

select cr.Name,rs.Name,nrs.Call_Type_ID,Count(*) As [Count]
from tb_lcr_rate_sheet rs (nolock)
inner join tb_lcr_carrier cr (nolock) on rs.Carrier_ID = cr.ID
inner join tb_lcr_work_sheet ws (nolock) on rs.ID = ws.Rate_Sheet_ID
inner join tb_lcr_normalized_rate_sheet nrs (nolock) on ws.ID = nrs.Work_Sheet_ID
where rs.ID = @rate_sheet_Id and nrs.Call_Type_ID IN ('INTERSTATE','INTRASTATE')
group by cr.Name,rs.Name,nrs.Call_Type_ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-11-10 : 11:13:51

Hello Visakh,

Thnks. I solved my issue.

Thanks,
Harish Patil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources