mardi 23 février 2016

Pivot in sql with multiple tables

I have a requirement to show values from multiple tables to a single row . CREATE TABLE [dbo].[Table1Result](
    [Table1ResultID] [int] IDENTITY(1,1) NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [CreatedByUserID] [int] NOT NULL,
    )


CREATE TABLE [dbo].[Table2Result](
    [Table2ResultID] [int] IDENTITY(1,1) NOT NULL,
    [Table1Result] [int] NOT NULL,  --primary key of table1
    [Table2Name] [nvarchar](255) NULL,
    [Table2Overall] [bit] NOT NULL,
    [Table2GenterID] [int] NULL,
    [CombinedTable2Outcome] [bit] NULL,
    [Display] [bit] NOT NULL,
    [IsActive] [bit] NOT NULL,
    [HasOutcome] [bit] NOT NULL,
        )


CREATE TABLE [dbo].[Table3Result](
    [Table3ResultID] [int] IDENTITY(1,1) NOT NULL,
    [Table3ResultID] [int] NOT NULL, --primary key of table 2 ..here foreigh key
    [Table3Text] [nvarchar](max) NULL,
    [Table3Value] [int] NULL,
    [Table3StringValue] [nvarchar](max) NULL,
    [Table3BooleanValue] [bit] NULL,
    [Table3DateTimeValue] [datetime] NULL,
    [Table3DecimalValue] [decimal](18, 2) NULL,
    [Table3Dropdown] [bit] NULL,
    [Table3Validation] [bit] NOT NULL,
    [Table3DetailID] [int] NULL,
    [Table3QuestionID] [int] NOT NULL,
    [CombinedTable3Outcome] [bit] NOT NULL,     
    )

Above tables are 3 tables among them . Here 1 st table will have only one row for a particulat table1resultid . There is a foreign key releation ship with the second table ie table2result. There for a table1resultid (from 1st table) there will be multiple records.similarly table2 and table3 are related and so on. My requirement is to display 1 row for a particular id. that is for a particulat table1resultid i need to fetch data from all the tables and display in a single row.I expect the column heading will come as column heading in my result.I googled it and found i can get it through pivot. I could not understand it thouroughly. If someone can help me to figure out it will make my day.

Aucun commentaire:

Enregistrer un commentaire