Friday 24 November 2017

Split Rows into Multiples Rows If they have more than One Counts.

I have a Input table with a Name and Counts Columns.
I want to Split my Name with multiple times based on Counts column values.
Please , Check an above Screen Short that i have pasted.

create table #SPLITROW 
(Name varchar(10)
,Counts int 
)
INSERT INTO #SPLITROW
VALUES ('AA',5)
INSERT INTO #SPLITROW
VALUES ('BB',4)
INSERT INTO #SPLITROW
VALUES ('CC',3)
INSERT INTO #SPLITROW
VALUES ('DD',2)
INSERT INTO #SPLITROW
VALUES ('EE',1)
declare @Rc as int
declare @inital as int=1
select @rc=max(Counts) from #SPLITROW
declare @rowTab as table
(Countss int)
while (@inital<=@Rc)
begin
insert into @rowTab values(@inital)
set @inital=@inital+1
end
SELECT Name,Counts
FROM #SPLITROW  sr
JOIN (select Countss as RN from @rowTab) AS  oft
ON oft.RN <= Counts
order by Name

Alternate Option

SELECT Name , Counts FROM @SPLITROW sr JOIN (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN FROM sys.columns) AS oft ON oft.RN <= Counts

Output Table

No comments:

Post a Comment