CODEWITHSUNDEEP
sqlsql-serversql-server-2012sql-server-2014
shael
shael

Reputation: 177

unpivot all columns

I required to unpivot as per below structure. and I reuired my output using unpivot SQL Query.

the script for current table : 

GO
/****** Object:  Table [dbo].[mayank_UnPivot]    Script Date: 12/20/2018 10:08:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[mayank_UnPivot](
    [paticular_L] [varchar](50) NULL,
    [debit_L] [decimal](10, 2) NULL,
    [credit_L] [decimal](10, 2) NULL,
    [particular_A] [varchar](50) NULL,
    [debit_A] [decimal](10, 2) NULL,
    [credit_A] [decimal](10, 2) NULL,
    [particular_FA] [varchar](50) NULL,
    [debit_FA] [decimal](10, 2) NULL,
    [credit_FA] [decimal](10, 2) NULL,
    [particular_LAA] [varchar](50) NULL,
    [debit_LAA] [decimal](10, 2) NULL,
    [credit_LAA] [decimal](10, 2) NULL,
    [particular_MEA] [varchar](50) NULL,
    [debit_MEA] [decimal](10, 2) NULL,
    [credit_MEA] [decimal](10, 2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[mayank_UnPivot] ([paticular_L], [debit_L], [credit_L], [particular_A], [debit_A], [credit_A], [particular_FA], [debit_FA], [credit_FA], [particular_LAA], [debit_LAA], [credit_LAA], [particular_MEA], [debit_MEA], [credit_MEA]) VALUES (N'Furniture', CAST(50000.00 AS Decimal(10, 2)), CAST(50000.00 AS Decimal(10, 2)), N'Sequrity Deposits', CAST(50000.00 AS Decimal(10, 2)), CAST(50000.00 AS Decimal(10, 2)), N'Machinery', CAST(1200000.00 AS Decimal(10, 2)), CAST(1200000.00 AS Decimal(10, 2)), N'Loan From Kotak Bank', CAST(3000000.00 AS Decimal(10, 2)), CAST(3000000.00 AS Decimal(10, 2)), N'Bank Service Charge', CAST(1000.00 AS Decimal(10, 2)), CAST(1000.00 AS Decimal(10, 2)))
GO

and table output is as below:

paticular_L debit_L     credit_L    particular_A        debit_A     credit_A    particular_FA   debit_FA    credit_FA   particular_LAA          debit_LAA   credit_LAA  particular_MEA      debit_MEA   credit_MEA
Furniture   50000.00    50000.00    Sequrity Deposits   50000.00    50000.00    Machinery       1200000.00  1200000.00  Loan From Kotak Bank    3000000.00  3000000.00  Bank Service Charge 1000.00     1000.00

i required output as below:

particular               debit        credit
Furniture                50000.00     50000.00  
Sequrity Deposits        50000.00     50000.00  
Machinery                1200000.00   1200000.00    
Loan From Kotak Bank     3000000.00   3000000.00    
Bank Service Charge      1000.00      1000.00

Upvotes: 0

Views: 438

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Just use cross apply:

select v.*
from mayank_UnPivot m cross apply
     (values (particular_L, debit_L, credit_L),
             (particular_A, debit_A, credit_A),
             (particular_FA, debit_FA, credit_FA),
             (particular_FAA, debit_FAA, credit_FAA),
             (particular_MEA, debit_MEA, credit_MEA)
     ) v(particular, debit, credit);

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

just use CROSS APPLY with UNION ALL

select  u.*
from    mayank_UnPivot 
        cross apply
        (
          select    paticular_L as particular, debit_L as debit, credit_L as credit
          union all
          select    particular_A as particular, debit_A as debit, credit_A as credit
          union all
          select    particular_FA as particular, debit_FA as debit, credit_FA as credit
          union all
          select    particular_LAA as particular, debit_LAA as debit, credit_LAA as credit
          union all
          select    particular_MEA as particular, debit_MEA as debit, credit_MEA as credit
        ) u

if you must use UNPIVOT

select  paticular, debit, credit
from    mayank_UnPivot 
        unpivot
        (
            paticular
            for col_name1 in ([paticular_L], [particular_A], [particular_FA], [particular_LAA], [particular_MEA])
        ) u
        unpivot
        (
            debit
            for col_name2 in (debit_L, debit_A, [debit_FA], [debit_LAA], [debit_MEA])
        ) u
        unpivot
        (
            credit
            for col_name3 in (credit_L, credit_A, [credit_FA], [credit_LAA], [credit_MEA])
        ) u
where   right(col_name1, 2) = right(col_name2, 2)
and     right(col_name1, 2) = right(col_name3, 2)

Upvotes: 1

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can try this.

SELECT particular.value particular, debit.value debit, credit.value credit  FROM 
    ( SELECT [value], REPLACE([key],'particular_', '') AS [key]
        FROM mayank_UnPivot
        UNPIVOT ( [value] FOR [key] IN ( [particular_L], [particular_A], [particular_FA], [particular_LAA], [particular_MEA] ) ) AS UPVT) AS particular
    LEFT JOIN 
    ( SELECT [value], REPLACE([key],'debit_', '') AS [key]
        FROM mayank_UnPivot
        UNPIVOT ( [value] FOR [key] IN ( [debit_L], [debit_A], [debit_FA], [debit_LAA], [debit_MEA] ) ) AS UPVT) AS debit 
            ON particular.[key] = debit.[key]
    LEFT JOIN 
    ( SELECT [value], REPLACE([key],'credit_', '') AS [key]
        FROM mayank_UnPivot
        UNPIVOT ( [value] FOR [key] IN ( [credit_L], [credit_A], [credit_FA], [credit_LAA], [credit_MEA] ) ) AS UPVT) AS credit 
            ON particular.[key] = credit.[key]

Result:

particular                 debit         credit        
-------------------------- ------------- ---------------
Furniture                  50000.00      50000.00
Sequrity Deposits          50000.00      50000.00
Machinery                  1200000.00    1200000.00
Loan From Kotak Bank       3000000.00    3000000.00
Bank Service Charge        1000.00       1000.00

Upvotes: 0

Related Questions