Select 3 records and display it as a single record

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

SQL Server 2008 Forums

Transact-SQL (2008)

Select 3 records and display it as a single record

Author

Topic

wnetra
Starting Member

3 Posts

Posted - 2012-07-17 : 07:21:02

NPA NXX Call_Type_ID Abbreviation_1 RATE_1 Abbreviation_2 RATE_2 Abbreviation_3 RATE_3
201 200 INTERSTATE ANI 0.004 BW 0.0042 ATele 0.0044
201 200 INTRASTATE ATele 0.0038 ANI 0.0051 ATANDT 0.0056
201 202 INTERSTATE ANI 0.0033 BM 0.0038 BW 0.0045
201 202 INTRASTATE ATANDT 0.0021 BM 0.0021 ANI 0.0029

I want dta into above format.
My sql statement is:

declare @TempTable table
( NPA varchar(10),
NXX varchar(10),
Call_Type_ID varchar(15),
Rate varchar(36),
Rank varchar(10),
Abbreviation varchar(36),
Routing_Guide_Details_ID varchar(36)
)
insert into @TempTable
SELECT
NRS.NPA,
NRS.NXX,
NRS.Call_Type_ID,
NRS.Rate,
RGLCC.Rank,
CA.Abbreviation,
RGLCC.Routing_Guide_Details_ID
FROM
tb_lcr_normalized_rate_sheet NRS INNER JOIN
tb_lcr_routing_guide_least_cost_carrier RGLCC ON
NRS.ID = RGLCC.Normalized_Rate_Sheet_ID INNER JOIN
tb_lcr_work_sheet WS ON NRS.Work_Sheet_ID = WS.ID INNER JOIN
tb_lcr_rate_sheet RS ON WS.Rate_Sheet_ID = RS.ID INNER JOIN
tb_lcr_carrier CA ON RS.Carrier_ID = CA.ID

select * from @TempTable

select
NPA,NXX,Call_Type_ID,
--Rate,
Abbreviation,
(CASE WHEN RANK = '1' THEN Abbreviation END) Abbreviation_1,
(CASE WHEN RANK = '1' THEN RATE END) as RATE_1,

(CASE WHEN RANK = '2' THEN Abbreviation END ) AS Abbreviation_2,
(CASE WHEN RANK = '2' THEN RATE END ) as RATE_2,

(CASE WHEN RANK = '3' THEN Abbreviation END ) AS Abbreviation_3,
(CASE when RANK = '3' THEN RATE END ) as RATE_3

from @TempTable
group by NPA,NXX,Call_Type_ID
,Rate,
Abbreviation
,RANK
--group by NPA,NXX,Call_Type_ID,
--,Rate,Abbreviation,
--RANK,Abbreviation

Result by above query:
NPA NXX Call_Type_ID Abbreviation Abbreviation_1 RATE_1 Abbreviation_2 RATE_2 Abbreviation_3 RATE_3
201 200 INTERSTATE ANI ANI 0.004 NULL NULL NULL NULL
201 200 INTERSTATE BW NULL NULL BW 0.0042 NULL NULL
201 200 INTERSTATE ATele NULL NULL NULL NULL ATele 0.0044
201 200 INTRASTATE ATele ATele 0.0038 NULL NULL NULL NULL
201 200 INTRASTATE ANI NULL NULL ANI 0.0051 NULL NULL
201 200 INTRASTATE ATANDT NULL NULL NULL NULL ATANDT 0.0056
201 202 INTERSTATE ANI ANI 0.0033 NULL NULL NULL NULL
201 202 INTERSTATE BM NULL NULL BM 0.0038 NULL NULL
201 202 INTERSTATE BW NULL NULL NULL NULL BW 0.0045
201 202 INTRASTATE ATANDT ATANDT 0.0021 NULL NULL NULL NULL
201 202 INTRASTATE BM NULL NULL BM 0.0021 NULL NULL
201 202 INTRASTATE ANI NULL NULL NULL NULL ANI 0.0029
703 736 INTERSTATE ANI ANI 0.008 NULL NULL NULL NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-17 : 09:52:02

[code]
select
NPA,NXX,Call_Type_ID,
MAX(CASE WHEN RANK = '1' THEN Abbreviation END) Abbreviation_1,
MAX(CASE WHEN RANK = '1' THEN RATE END) as RATE_1,

MAX(CASE WHEN RANK = '2' THEN Abbreviation END ) AS Abbreviation_2,
MAX(CASE WHEN RANK = '2' THEN RATE END ) as RATE_2,

MAX(CASE WHEN RANK = '3' THEN Abbreviation END ) AS Abbreviation_3,
MAX(CASE when RANK = '3' THEN RATE END ) as RATE_3

from @TempTable
group by NPA,NXX,Call_Type_ID
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources