TechSapphire Tuesday, 2018-07-17, 2:39 PM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • Horizontal table output in SSRS or RDLC using CROSS APPLY

    To display output in horizontal format. I have used cross apply in this example because in this case CROSS APPLY suits best. PIVOT is used when there is aggregation or to split column. UNPIVOT is used to change orientation but no. of columns need to be fixed. Even if you make it dynamic then also you can not use it in SSRS as SSRS need static dataset or rowset in old version with fixed number of columns predefined so that it could be saved with report definition. Script is mentioned below:

    USE [Northwind]
    GO
    /****** Object:  Table [dbo].[Table_1]    Script Date: 10/22/2015 10:03:26 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Table_1](
        [id] [int] NOT NULL,
        [Data1] [varchar](50) NULL,
        [Data2] [varchar](50) NULL,
     CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
    (
        [id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT [dbo].[Table_1] ([id], [Data1], [Data2]) VALUES (1, N'12', N'43')
    INSERT [dbo].[Table_1] ([id], [Data1], [Data2]) VALUES (2, N'3234', N'344')
    INSERT [dbo].[Table_1] ([id], [Data1], [Data2]) VALUES (3, N'4', N'4')
    GO
    CREATE PROC GETDATAHORI
    AS
    select t2.id, t.Title,t.Value from Table_1 t2
    cross apply
    (
    values('Data1',data1),
    ('Data2',data2)
    )t([Title],[Value])
    select * from Table_1

     

    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2018