October 13, 2005 at 10:05 pm
Hi,
I have problem for multiple rows to single field.I have table named TBL_Damage in that for multiple vehicle number multiple damage is there.I want these damage into one field separated by comma.
The Table is like this.
Damage VehicleNo
1 KA 122
2 KA 122
1 KA 111
2 KA 111
I wnat the output like this
KA 122 1,2
KA 111 1,2
Can any one help me for the SQL Query.
Thanks and Regards
AJOSH
October 17, 2005 at 8:00 am
This was removed by the editor as SPAM
October 18, 2005 at 11:26 pm
I have think of two solutions:
Solution 1:
Declare @VehicleNo varchar(10), @Damages Varchar(50)
Create table #Damages (VehicleNo Varchar(10) , Damages Varchar(50))
Select @VehicleNo =''
Select @VehicleNo = Min(VehicleNo) from TBL_Damage Where VehicleNo >@VehicleNo
While @VehicleNo is NOT NULL
Begin
Set @Damages =''
Select @Damages = @Damages+','+Convert(Varchar, Damage) From TBL_Damage where VehicleNo = @VehicleNo order by Damage
Set @Damages = Substring(@Damages, 2, Len(@Damages)-1)
Insert #Damages Values (@VehicleNo, @Damages)
Select @VehicleNo = Min(VehicleNo) from TBL_Damage Where VehicleNo >@VehicleNo
END
Select * from #Damages
Drop table #Damages
Solution 2:
Declare @VehicleNo varchar(10), @Damages Varchar(50)
Create table #Damages (VehicleNo Varchar(10) , Damages Varchar(50), Lastdamage int)
Insert #Damages Select VehicleNo, min(Damage), Min(Damage) from tbl_damage Group by VehicleNo
While Exists(Select A.damage from tbl_damage A inner join #Damages B On A.VehicleNo = B.VehicleNo and A.Damage >B.Lastdamage)
Begin
Update #Damages Set Damages = Damages + ','+Convert(varchar, A.Damage), Lastdamage = A.Damage
from (Select A.VehicleNo, Min(Damage) Damage
From tbl_damage A inner join
#Damages B
On A.VehicleNo = B.VehicleNo and A.Damage >B.Lastdamage
Group By A.vehicleNo) A
Where A.VehicleNo = #Damages.VehicleNo
END
Select VehicleNo, Damages from #Damages
Drop table #Damages
I suggest you to try both solutions and choose the better solution.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
October 18, 2005 at 11:59 pm
In addition to the above pure TSQL methods, you can try another option using any of the .net language.
SQL Server 2005 comes with another new feature called user defined aggregates. You can write the aggregate in VB.Net or C# 9or any other .net language) and add the assembly into sql Server. Please check the following sites:
http://msdn2.microsoft.com/en-us/library/91e6taax
http://www.novicksoftware.com/Articles/sql-2005-product-user-defined-aggregate.htm
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
October 19, 2005 at 2:39 am
Hi,
Thanks for it.
Now I have another problem.
Now I am Passing 4 Parameters to my Store Proc.
Here is my Store Proc
Using a User define Function Callad UnitBelow will retrive some data like This
251,252,253,254
Based on this string value I have to itrate one by one and have to set as the @Unit_ID variable.
I am facing probleing that The loop will not take the last one.Can any body Help me..
CREATE procedure Test_Report
(
@Type_ID varchar(8000),
@Unit_ID varchar(8000),
@FromDate varchar(20),
@Todate varchar(20)
)
AS
BEGIN
DECLARE @PWeekFrom varchar(20),@PWeekTo varchar(20)
Declare @PYearFrom varchar(20),@PYearTo varchar(20)
Declare @CYearFrom varchar(20),@CYearTo varchar(20)
--declare @FromDate varchar(10),@Todate varchar(10)
declare @UnitID varchar(8000)
select @UnitID=dbo.unitbelow(@Unit_ID) from tbl_unit_Master where unit_ID = @Unit_ID
declare @Str1 varchar(50),@str2 varchar(50),@str3 varchar(50)
select @Str1= mid from tbl_general_master where pid =(select mid from tbl_general_master where local_description = 'Pedestrian injury' )and Local_description = 'Fatal'
select @Str2= mid from tbl_general_master where pid =(select mid from tbl_general_master where local_description = 'Pedestrian injury' )and Local_description = 'Minor'
select @Str3= mid from tbl_general_master where pid =(select mid from tbl_general_master where local_description = 'Pedestrian injury' )and Local_description = 'Serious'
set @PWeekFrom=convert(varchar,dateadd(day,-14,@todate),101)
set @PWeekTo = convert(varchar,dateadd(day,-7,@todate),101)
set @PYearFrom=convert(varchar,dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,@todate),0)),101)
set @PYearTo= convert(varchar,dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,@todate),0)),101)
set @CYearFrom =convert(varchar,DATEADD(yy, DATEDIFF(yy,0,@todate), 0),103)
set @CYearTo = convert(varchar,@todate,103)
set @FromDate =convert(varchar,@FromDate,103)
declare @delimiter nchar(1)--
set @delimiter = ','
DECLARE @pos int
DECLARE @tmpval varchar(8000),@last varchar(50)
SET @pos = charindex(@delimiter, @UnitID)
WHILE @pos > 0
BEGIN
SET @tmpval = left(@UnitID, charindex(@delimiter, @UnitID) - 1)
SET @Unit_ID= @tmpval
----TO fetch Unit Name
select Unit_Name from tbl_Unit_Master Where Unit_ID=@Unit_ID
---Current Week
select distinct count(case when a.Injury_Type= @Str1 then 1 else 0 end) 'Fatal Currentweek' ,
(count(case when a.Injury_Type=@Str2 then 1 else 0 end) + count(case when a.Injury_Type=@Str3 then 1 else 0 end)) 'Non Fatal CurrentWeek'
from TBL_TR_ACCIDENT_CASUALTIES a,tbl_unit_master b ,Tbl_TR_accident_report AR
where type_id=@Type_ID and a.unit_id=@Unit_ID and
AR.Accident_date Between @FromDate and @CYearTo
group by unit_name
--Previous Week
select distinct count(case when a.Injury_Type=@Str1 then 1 else 0 end) 'Fatal Pweek' ,
(count(case when a.Injury_Type=@Str2 then 1 else 0 end) + count(case when a.Injury_Type=@Str3 then 1 else 0 end)) 'Non Fatal PWeek'
from TBL_TR_ACCIDENT_CASUALTIES a,tbl_unit_master b ,Tbl_TR_accident_report AR
where type_id=@Type_ID and a.unit_id=@Unit_ID and
AR.Accident_date Between @PWeekFrom and @PWeekTo
group by unit_name
--Current Year
select distinct count(case when a.Injury_Type=@Str1 then 1 else 0 end) 'Fatal CYear' ,
(count(case when a.Injury_Type=@Str2 then 1 else 0 end) + count(case when a.Injury_Type=@Str3 then 1 else 0 end)) 'Non Fatal CYear'
from TBL_TR_ACCIDENT_CASUALTIES a,tbl_unit_master b ,Tbl_TR_accident_report AR
where type_id=@Type_ID and a.unit_id=@Unit_ID and
AR.Accident_date Between @CYearFrom and @CYearTo
group by unit_name
---Previous Year
select distinct count(case when a.Injury_Type=@Str1 then 1 else 0 end) 'Fatal PYear' ,
count(case when a.Injury_Type=@Str2 or a.Injury_Type=@Str3 then 1 else 0 end) 'Non Fatal PYear'
from TBL_TR_ACCIDENT_CASUALTIES a,tbl_unit_master b ,Tbl_TR_accident_report AR
where type_id=@Type_ID and a.unit_id=@Unit_ID and
AR.Accident_date Between @PYearFrom and @PYearTo
group by unit_name
SET @UnitID = substring(@UnitID, @pos + 1, len(@UnitID))
SET @pos = charindex(@delimiter, @UnitID)
IF (@pos = 0)
begin
set @Unit_ID = @UnitID
-- set @pos=1
GOTO table_loop
End
END
end
GO
Thanks and regards
Ajosh
October 19, 2005 at 2:39 am
Hi,
Thanks for it.
Now I have another problem.
Now I am Passing 4 Parameters to my Store Proc.
Here is my Store Proc
Using a User define Function Callad UnitBelow will retrive some data like This
251,252,253,254
Based on this string value I have to itrate one by one and have to set as the @Unit_ID variable.
I am facing probleing that The loop will not take the last one.Can any body Help me..
CREATE procedure Test_Report
(
@Type_ID varchar(8000),
@Unit_ID varchar(8000),
@FromDate varchar(20),
@Todate varchar(20)
)
AS
BEGIN
DECLARE @PWeekFrom varchar(20),@PWeekTo varchar(20)
Declare @PYearFrom varchar(20),@PYearTo varchar(20)
Declare @CYearFrom varchar(20),@CYearTo varchar(20)
--declare @FromDate varchar(10),@Todate varchar(10)
declare @UnitID varchar(8000)
select @UnitID=dbo.unitbelow(@Unit_ID) from tbl_unit_Master where unit_ID = @Unit_ID
declare @Str1 varchar(50),@str2 varchar(50),@str3 varchar(50)
select @Str1= mid from tbl_general_master where pid =(select mid from tbl_general_master where local_description = 'Pedestrian injury' )and Local_description = 'Fatal'
select @Str2= mid from tbl_general_master where pid =(select mid from tbl_general_master where local_description = 'Pedestrian injury' )and Local_description = 'Minor'
select @Str3= mid from tbl_general_master where pid =(select mid from tbl_general_master where local_description = 'Pedestrian injury' )and Local_description = 'Serious'
set @PWeekFrom=convert(varchar,dateadd(day,-14,@todate),101)
set @PWeekTo = convert(varchar,dateadd(day,-7,@todate),101)
set @PYearFrom=convert(varchar,dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,@todate),0)),101)
set @PYearTo= convert(varchar,dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,@todate),0)),101)
set @CYearFrom =convert(varchar,DATEADD(yy, DATEDIFF(yy,0,@todate), 0),103)
set @CYearTo = convert(varchar,@todate,103)
set @FromDate =convert(varchar,@FromDate,103)
declare @delimiter nchar(1)--
set @delimiter = ','
DECLARE @pos int
DECLARE @tmpval varchar(8000),@last varchar(50)
SET @pos = charindex(@delimiter, @UnitID)
WHILE @pos > 0
BEGIN
SET @tmpval = left(@UnitID, charindex(@delimiter, @UnitID) - 1)
SET @Unit_ID= @tmpval
----TO fetch Unit Name
select Unit_Name from tbl_Unit_Master Where Unit_ID=@Unit_ID
---Current Week
select distinct count(case when a.Injury_Type= @Str1 then 1 else 0 end) 'Fatal Currentweek' ,
(count(case when a.Injury_Type=@Str2 then 1 else 0 end) + count(case when a.Injury_Type=@Str3 then 1 else 0 end)) 'Non Fatal CurrentWeek'
from TBL_TR_ACCIDENT_CASUALTIES a,tbl_unit_master b ,Tbl_TR_accident_report AR
where type_id=@Type_ID and a.unit_id=@Unit_ID and
AR.Accident_date Between @FromDate and @CYearTo
group by unit_name
--Previous Week
select distinct count(case when a.Injury_Type=@Str1 then 1 else 0 end) 'Fatal Pweek' ,
(count(case when a.Injury_Type=@Str2 then 1 else 0 end) + count(case when a.Injury_Type=@Str3 then 1 else 0 end)) 'Non Fatal PWeek'
from TBL_TR_ACCIDENT_CASUALTIES a,tbl_unit_master b ,Tbl_TR_accident_report AR
where type_id=@Type_ID and a.unit_id=@Unit_ID and
AR.Accident_date Between @PWeekFrom and @PWeekTo
group by unit_name
--Current Year
select distinct count(case when a.Injury_Type=@Str1 then 1 else 0 end) 'Fatal CYear' ,
(count(case when a.Injury_Type=@Str2 then 1 else 0 end) + count(case when a.Injury_Type=@Str3 then 1 else 0 end)) 'Non Fatal CYear'
from TBL_TR_ACCIDENT_CASUALTIES a,tbl_unit_master b ,Tbl_TR_accident_report AR
where type_id=@Type_ID and a.unit_id=@Unit_ID and
AR.Accident_date Between @CYearFrom and @CYearTo
group by unit_name
---Previous Year
select distinct count(case when a.Injury_Type=@Str1 then 1 else 0 end) 'Fatal PYear' ,
count(case when a.Injury_Type=@Str2 or a.Injury_Type=@Str3 then 1 else 0 end) 'Non Fatal PYear'
from TBL_TR_ACCIDENT_CASUALTIES a,tbl_unit_master b ,Tbl_TR_accident_report AR
where type_id=@Type_ID and a.unit_id=@Unit_ID and
AR.Accident_date Between @PYearFrom and @PYearTo
group by unit_name
SET @UnitID = substring(@UnitID, @pos + 1, len(@UnitID))
SET @pos = charindex(@delimiter, @UnitID)
IF (@pos = 0)
begin
set @Unit_ID = @UnitID
-- set @pos=1
GOTO table_loop
End
END
end
GO
Thanks and regards
Ajosh
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply