Use unpivot to convert columns into rows

In a previous blog we converted rows into columns using pivot, now this time i needed to convert columns into rows. This was simply achieved using the unpivot functionality in sql server.

declare @tbl table(name varchar(50), amt1 numeric(12,2), 
    amt2 numeric(12,2))
    
insert into @tbl(name, amt1, amt2)values('ppv', 1, 3)
insert into @tbl(name, amt1, amt2)values('ppvs', 41, 43)

select * from @tbl 

select * from @tbl unpivot
 ([Amt] for Types in (amt1, amt2)) as unpv

 

 

Credits:

Thanks to Sachin for this!!

Running total with cross join

Declare @tbl table (branch varchar(100), date date, amt numeric(12,2))insert into @tbl(branch, date, amt)select Branch, DATE, SUM(amount) from RPT_PledgedAmount awhere a.Branch in ('f001','F008','F009','F010','F011','F012','F013','F014')group
order
by Branch, DATE by Branchselect
group a.branch, a.date, a.amt, sum(b.amt) from @tbl a cross join @tbl b where a.branch = b.branch and b.date <= a.date by a.branch, a.date, a.amtorder by a.branch,a.date

Sql Server

From this string
'D:\DBF_004_11_12_14_V11\FDMS\024.dbf' we need to extract the file name 024

Declare @fileName Varchar(500)set @fileName = 'D:\DBF_004_11_12_14_V11\FDMS\024.dbf'select LEFT(RIGHT(@fileName , charINDEX('\', reverse(@fileName )) - 1), 3)

Split string into a table using table valued functions

create Function dbo.fn_split(@Message Varchar(1000), @delimiter char(1), @index int ) Returns @TblSplit Table(Id Int Identity(1,1),Value
)Varchar(500)As
Begin
Declare
@curIndex
@pos
@prevPos
@result





int = 0, int = 1, int = 0, varchar(1000) while @pos > 0 Begin set @pos = CHARINDEX(@delimiter, @Message, @prevPos); if(@pos > 0) begin-- get the chars between the prev position to next delimiter pos


set @result = SUBSTRING(@message, @prevPos, @pos-@prevPos) end else begin--get last delim message


set @result = SUBSTRING(@message, @prevPos, LEN(@message)) end iNSERT INTO @TblSplit(Value) vALUES(@result)



set @prevPos = @pos + 1 set @curIndex = @curIndex + 1; end return; End

test:
select * FROM dbo.fn_SPLIT('D:\DBF_004_11_12_14_V11\FDMS\024.dbf','\', 2)

Get datetime in YYYYMM format–Sql Server

 

I need to get the date in YYYYMM format i.e. ‘2011-01-23’ becomes ‘201101’

declare @date date = '2016-11-23'
select  convert(varchar(6), @date, 112)

Get Running Total with CTE

 

Declare @Table table (id int identity(1,1), amount numeric(12,2), run_total numeric(12,2))
Insert into @Table(amount) Values( 1000)
Insert into @Table(amount) Values( 2000)
Insert into @Table(amount) Values(4000)
Insert into @Table(amount) Values(4000)
Insert into @Table(amount) Values(6000)
Insert into @Table(amount) Values(3000)


;With CTE_Tbl(id, amount, run_total)
As
(
    select id, amount, amount as run_total 
        from @Table t where id = 1
    union all 
    select t1.id, t1.amount, cast (t1.amount + c.run_total as numeric(12,2)) as rt 
     from @Table t1 
     join CTE_Tbl c on c.id+1 = t1.id    
)
select * from CTE_Tbl

Sql Function to display Financial Quarter

Create Function fn_FinancialQuarter(@Date date )Returns Varchar(50)As
BeginDeclare @Quarter Varchar(50)Declare @Month Int = MOnth(@Date)
If(@Month <= 3)BeginSET @Quarter = 'Q-4 ' + Cast(YEAR(DATEADD(Year, -1, @date)) as varchar(10))EndElseBeginSET @Quarter = 'Q ' + Cast(((MONTH(@Date) - 4) / 3) + 1 as varchar(50)) + '-' + cast(YEAR(@date) as varchar(10))EndReturn @QuarterEnd

Use CTE & Row_Number to update the Row Number

I had a table where i needed the row number and i had forgotten to insert it. I got this solved using CTE and the Row_Number function.

Declare @test table (name varchar(50), rowno int)
insert into @test (name, rowno) Values ('pramod', 1)
insert into @test (name, rowno) Values ('dileep', 1)
insert into @test (name, rowno) Values ('hari', 1);

with r_SomeTable
as
(
select *
, row_number() over(order by name) as rnk
from @test
)
update r_SomeTable
set rowno = rnk;
select * from @test