CTE explanation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CTE explanation

Author

Topic

zain2
Starting Member

16 Posts

Posted - 2011-03-17 : 21:22:46

Please can someone breakdown this query and give some kind of explanation about and what is happening in this query, there is select r.* and than select again.... i can't figure it out this bit....


WITH  UnpivotData
     AS ( SELECT  R.*
        FROM   ( SELECT  *
             FROM   @LCS
            ) Q UNPIVOT ( [value] FOR [type] IN ( Q.pgifc1, Q.pgifc2, Q.pgifc3, Q.pgifc4 ) ) R
        WHERE  NOT R.[value] IS NULL
       ),
    Items
     AS ( SELECT  *
        FROM   UnpivotData
        WHERE  Code = @SearchFor
       )

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-18 : 03:36:40

This


SELECT  *
             FROM   @LCS
            ) Q UNPIVOT ( [value] FOR [type] IN ( Q.pgifc1, Q.pgifc2, Q.pgifc3, Q.pgifc4 )

unpivots the data. From the data generated, this query

SELECT  R.*
        FROM   ( SELECT  *
             FROM   @LCS
            ) Q UNPIVOT ( [value] FOR [type] IN ( Q.pgifc1, Q.pgifc2, Q.pgifc3, Q.pgifc4 ) ) R
        WHERE  NOT R.[value] IS NULL

will filter the resultset with [vale] is not null. R is a alias name for the derived table.

This following code uses the above as datasource and filter the data with code=@SearchFor

Items
     AS ( SELECT  *
        FROM   UnpivotData
        WHERE  Code = @SearchFor
       )

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources