Creating a hierarchical view with recursion

Hot Topic

Basic SQL Execution Question
Dedicated Administrator Connection
stored procedures, compile or not
If statment within Where clause
Updating Data in Oracle
Data entry in a datetime field
db_dtsadmin role
operating system does not support encryption
Can anyone help with a basic Analysis Services cube question?
IIS detection on Vista: Where's my Windows Authentication option ?

SQL Server

How to automatic update view when table's structure has changed
conversion from nText to varchar
Multiple Dynamic Insert Statements in one stored procedure?
Left Outer Join versus Subquery
test deployed packages
rsReportNotReady with subreports
Error during install of SqlServer 2005 64 Bit edition - please help!
SQL 2005 Enterprise edition The image file Setup.exe is valid, but is for a machine type other than the current machine
Help with converting code: VB code in SQL Server 2000->Visual Studio BI 2005
Top 10 with ties

Creating a hierarchical view with recursion

Say, I have a table with 3 columns
===============
Col1, Col2, Col3
==============
r11, r12, r13
R21,r22,r23
R31,r32,r33
:
:
(rows)
:

I want to create a view that looks as follows

========================================================
Col1_2_3, Col1_2, Col2_3, col1, col2, col3
========================================================
R11_r12_r13 r11_r12 r12_r13, r11, r12, r13
R21_r22_r23 r21_r22 r22_r23, r21, r22, r23
:
:


The idea being that col_1_2 appends the strings in columns 1 & 2, col2_3 for columns 2 & 3 and so on...How would I do that in a recursive manner so that it works for tables with any number of columns

Can anyone please help




Answer this question

Creating a hierarchical view with recursion

  • MrBrilliant

    You can create view based on only one query. Considering recursion within a query, it's only CTE available in MSSQL 2005.

  • rpskumar

    This is awesome! This looks like it might be what I am looking for.

    Let me try this and get back to you...



    regards



  • Flack

    Foob:

    What you seem more interested in is a more general method. If you will browse this msdn you should find some interesting posts. In addition, I have a couple of examples here. I am not sure if you are more interested in the faster code or the more readable code. Is this in the ballpark


    Dave


    use tempdb

    drop table dbo.mock
    go
    drop view dbo.vMockStringer
    go

    create table dbo.mock
    ( rid integer not null,
    col_01 varchar (10) not null,
    col_02 varchar (10) not null,
    col_03 varchar (10) not null,
    col_04 varchar (10) not null,
    col_05 varchar (10) not null,
    col_06 varchar (10) not null,
    col_07 varchar (10) not null,
    col_08 varchar (10) not null,
    col_09 varchar (10) not null,
    col_10 varchar (10) not null,
    col_11 varchar (10) not null,
    col_12 varchar (10) not null,
    col_13 varchar (10) not null,
    col_14 varchar (10) not null,
    col_15 varchar (10) not null,
    col_16 varchar (10) not null,
    col_17 varchar (10) not null,
    col_18 varchar (10) not null,
    col_19 varchar (10) not null,
    col_20 varchar (10) not null,
    col_21 varchar (10) not null,
    col_22 varchar (10) not null,
    col_23 varchar (10) not null,
    col_24 varchar (10) not null,
    col_25 varchar (10) not null,
    col_26 varchar (10) not null,
    col_27 varchar (10) not null,
    col_28 varchar (10) not null,
    col_29 varchar (10) not null,
    col_30 varchar (10) not null,

    constraint pk_mock primary key (rid)
    )
    go

    create view dbo.vMockStringer
    as

    select rid,
    col_01 as [1],
    col_02 as [2],
    col_03 as [3],
    col_04 as [4],
    col_05 as [5],
    col_06 as Devil,
    col_07 as [7],
    col_08 as Music,
    col_09 as [9],
    col_10 as [10],
    col_11 as [11],
    col_12 as [12],
    col_13 as [13],
    col_14 as [14],
    col_15 as [15],
    col_16 as [16],
    col_17 as [17],
    col_18 as [18],
    col_19 as [19],
    col_20 as [20],
    col_21 as [21],
    col_22 as [22],
    col_23 as [23],
    col_24 as [24],
    col_25 as [25],
    col_26 as [26],
    col_27 as [27],
    col_28 as [28],
    col_29 as [29],
    col_30 as [30]
    from dbo.mock
    go


    -- -------------------------------------------------------------
    -- Use table master.dbo.spt_values as a "numbers" table to
    -- generate the data. This table should NOT be used as a
    -- "numbers" table for production work!
    -- -------------------------------------------------------------
    insert into mock
    select number+1,
    '(' + convert (varchar(4), number+1) + ',1)',
    '(' + convert (varchar(4), number+1) + ',2)',
    '(' + convert (varchar(4), number+1) + ',3)',
    '(' + convert (varchar(4), number+1) + ',4)',
    '(' + convert (varchar(4), number+1) + ',5)',
    '(' + convert (varchar(4), number+1) + ',6)',
    '(' + convert (varchar(4), number+1) + ',7)',
    '(' + convert (varchar(4), number+1) + ',8)',
    '(' + convert (varchar(4), number+1) + ',9)',
    '(' + convert (varchar(4), number+1) + ',10)',
    '(' + convert (varchar(4), number+1) + ',11)',
    '(' + convert (varchar(4), number+1) + ',12)',
    '(' + convert (varchar(4), number+1) + ',13)',
    '(' + convert (varchar(4), number+1) + ',14)',
    '(' + convert (varchar(4), number+1) + ',15)',
    '(' + convert (varchar(4), number+1) + ',16)',
    '(' + convert (varchar(4), number+1) + ',17)',
    '(' + convert (varchar(4), number+1) + ',18)',
    '(' + convert (varchar(4), number+1) + ',19)',
    '(' + convert (varchar(4), number+1) + ',20)',
    '(' + convert (varchar(4), number+1) + ',21)',
    '(' + convert (varchar(4), number+1) + ',22)',
    '(' + convert (varchar(4), number+1) + ',23)',
    '(' + convert (varchar(4), number+1) + ',24)',
    '(' + convert (varchar(4), number+1) + ',25)',
    '(' + convert (varchar(4), number+1) + ',26)',
    '(' + convert (varchar(4), number+1) + ',27)',
    '(' + convert (varchar(4), number+1) + ',28)',
    '(' + convert (varchar(4), number+1) + ',29)',
    '(' + convert (varchar(4), number+1) + ',30)'
    from master.dbo.spt_values (nolock)
    where name is null
    --select * from vMockStringer

    declare @begDt datetime set @begDt = getdate()

    -- -------------------------------------------------------------
    -- This example shows how to get a "concatenated string
    -- product" from all of the columns that are in a given
    -- "column range" defined by variables "@stringFirst" and
    -- "@stringLast.
    --
    -- This specific query only returns the top 5 rows; comment
    -- out the "TOP 5" clause to see all of the rows.
    -- -------------------------------------------------------------
    declare @stringFirst integer set @stringFirst = 19
    declare @stringLast integer set @stringLast = 30

    select top 5
    rid,
    @stringFirst as stringFirst,
    @stringLast as stringLast,
    '{'
    + case when @stringFirst <= 1 and @stringLast >= 1 then col_01 else '' end
    + case when @stringFirst <= 1 and @stringLast > 1 then ',' else '' end
    + case when @stringFirst <= 2 and @stringLast >= 2 then col_02 else '' end
    + case when @stringFirst <= 2 and @stringLast > 2 then ',' else '' end
    + case when @stringFirst <= 3 and @stringLast >= 3 then col_03 else '' end
    + case when @stringFirst <= 3 and @stringLast > 3 then ',' else '' end
    + case when @stringFirst <= 4 and @stringLast >= 4 then col_04 else '' end
    + case when @stringFirst <= 4 and @stringLast > 4 then ',' else '' end
    + case when @stringFirst <= 5 and @stringLast >= 5 then col_05 else '' end
    + case when @stringFirst <= 5 and @stringLast > 5 then ',' else '' end
    + case when @stringFirst <= 6 and @stringLast >= 6 then col_06 else '' end
    + case when @stringFirst <= 6 and @stringLast > 6 then ',' else '' end
    + case when @stringFirst <= 7 and @stringLast >= 7 then col_07 else '' end
    + case when @stringFirst <= 7 and @stringLast > 7 then ',' else '' end
    + case when @stringFirst <= 8 and @stringLast >= 8 then col_08 else '' end
    + case when @stringFirst <= 8 and @stringLast > 8 then ',' else '' end
    + case when @stringFirst <= 9 and @stringLast >= 9 then col_09 else '' end
    + case when @stringFirst <= 9 and @stringLast > 9 then ',' else '' end
    + case when @stringFirst <= 10 and @stringLast >= 10 then col_10 else '' end
    + case when @stringFirst <= 10 and @stringLast > 10 then ',' else '' end
    + case when @stringFirst <= 11 and @stringLast >= 11 then col_11 else '' end
    + case when @stringFirst <= 11 and @stringLast > 11 then ',' else '' end
    + case when @stringFirst <= 12 and @stringLast >= 12 then col_12 else '' end
    + case when @stringFirst <= 12 and @stringLast > 12 then ',' else '' end
    + case when @stringFirst <= 13 and @stringLast >= 13 then col_13 else '' end
    + case when @stringFirst <= 13 and @stringLast > 13 then ',' else '' end
    + case when @stringFirst <= 14 and @stringLast >= 14 then col_14 else '' end
    + case when @stringFirst <= 14 and @stringLast > 14 then ',' else '' end
    + case when @stringFirst <= 15 and @stringLast >= 15 then col_15 else '' end
    + case when @stringFirst <= 15 and @stringLast > 15 then ',' else '' end
    + case when @stringFirst <= 16 and @stringLast >= 16 then col_16 else '' end
    + case when @stringFirst <= 16 and @stringLast > 16 then ',' else '' end
    + case when @stringFirst <= 17 and @stringLast >= 17 then col_17 else '' end
    + case when @stringFirst <= 17 and @stringLast > 17 then ',' else '' end
    + case when @stringFirst <= 18 and @stringLast >= 18 then col_18 else '' end
    + case when @stringFirst <= 18 and @stringLast > 18 then ',' else '' end
    + case when @stringFirst <= 19 and @stringLast >= 19 then col_19 else '' end
    + case when @stringFirst <= 19 and @stringLast > 19 then ',' else '' end
    + case when @stringFirst <= 20 and @stringLast >= 20 then col_20 else '' end
    + case when @stringFirst <= 20 and @stringLast > 20 then ',' else '' end
    + case when @stringFirst <= 21 and @stringLast >= 21 then col_21 else '' end
    + case when @stringFirst <= 21 and @stringLast > 21 then ',' else '' end
    + case when @stringFirst <= 22 and @stringLast >= 22 then col_22 else '' end
    + case when @stringFirst <= 22 and @stringLast > 22 then ',' else '' end
    + case when @stringFirst <= 23 and @stringLast >= 23 then col_23 else '' end
    + case when @stringFirst <= 23 and @stringLast > 23 then ',' else '' end
    + case when @stringFirst <= 24 and @stringLast >= 24 then col_24 else '' end
    + case when @stringFirst <= 24 and @stringLast > 24 then ',' else '' end
    + case when @stringFirst <= 25 and @stringLast >= 25 then col_25 else '' end
    + case when @stringFirst <= 25 and @stringLast > 25 then ',' else '' end
    + case when @stringFirst <= 26 and @stringLast >= 26 then col_26 else '' end
    + case when @stringFirst <= 26 and @stringLast > 26 then ',' else '' end
    + case when @stringFirst <= 27 and @stringLast >= 27 then col_27 else '' end
    + case when @stringFirst <= 27 and @stringLast > 27 then ',' else '' end
    + case when @stringFirst <= 28 and @stringLast >= 28 then col_28 else '' end
    + case when @stringFirst <= 28 and @stringLast > 28 then ',' else '' end
    + case when @stringFirst <= 29 and @stringLast >= 29 then col_29 else '' end
    + case when @stringFirst <= 29 and @stringLast > 29 then ',' else '' end
    + case when @stringFirst <= 30 and @stringLast >= 30 then col_30 else '' end
    + '}'
    as results
    from mock

    print ' '
    select datediff (ms, @begDt, getdate()) as [Elapsed Time]


    -- ------------ Sample Output: ------------

    -- rid stringFirst stringLast results
    -- ----------- ----------- ----------- --------------------------------------------------------------------------------------
    -- 1 19 30 {(1,19),(1,20),(1,21),(1,22),(1,23),(1,24),(1,25),(1,26),(1,27),(1,28),(1,29),(1,30)}
    -- 2 19 30 {(2,19),(2,20),(2,21),(2,22),(2,23),(2,24),(2,25),(2,26),(2,27),(2,28),(2,29),(2,30)}
    -- 3 19 30 {(3,19),(3,20),(3,21),(3,22),(3,23),(3,24),(3,25),(3,26),(3,27),(3,28),(3,29),(3,30)}
    -- 4 19 30 {(4,19),(4,20),(4,21),(4,22),(4,23),(4,24),(4,25),(4,26),(4,27),(4,28),(4,29),(4,30)}
    -- 5 19 30 {(5,19),(5,20),(5,21),(5,22),(5,23),(5,24),(5,25),(5,26),(5,27),(5,28),(5,29),(5,30)}

    -- Elapsed Time
    -- ------------
    -- 16


    set @begDt = getdate()
    -- -------------------------------------------------------------
    -- In this example we string out an ad hoc list of columns.
    -- We load the list of columns that we want to select into
    -- the @stringBitmap and use the bitmap in the select
    -- select statement to determine what columns get included
    -- in the "string product".
    --
    -- The @firstColumn variable is used to make it easier to
    -- determine what column does NOT get preceded by a comma.
    --
    -- In this case, we want to include columns 1,3,7,19 and 30.
    -- We indicate a column is included by "ORing" in that
    -- particular column. I deliberately skip the ZERO bit.
    -- There is no real reason for this other than I just feel
    -- like it. Weird.
    --
    -- The @columnList variable is deliberately too short so that
    -- the output of this select is more compact.
    -- -------------------------------------------------------------
    declare @stringBitmap integer
    declare @firstColumn integer set @firstColumn = 1
    --declare @columnList varchar (100) set @columnList = '1,3,7,19,30'
    declare @columnList varchar (15) set @columnList = '1,3,7,19,30'

    set @stringBitmap = 0
    | power (2, 1)
    | power (2, 3)
    | power (2, 7)
    | power (2, 19)
    | power (2, 30)
    --select @stringBitmap as [@sgtringBitmap]


    select top 5
    rid,
    @columnList as columnList,
    '{'
    + case when power(2, 1) & @stringBitmap = 0 then '' else col_01 end
    + case when power(2, 2) & @stringBitmap > 0
    and @firstColumn < 2 then ',' else '' end
    + case when power(2, 2) & @stringBitmap = 0 then '' else col_02 end
    + case when power(2, 3) & @stringBitmap > 0
    and @firstColumn < 3 then ',' else '' end
    + case when power(2, 3) & @stringBitmap = 0 then '' else col_03 end
    + case when power(2, 4) & @stringBitmap > 0
    and @firstColumn < 4 then ',' else '' end
    + case when power(2, 4) & @stringBitmap = 0 then '' else col_04 end
    + case when power(2, 5) & @stringBitmap > 0
    and @firstColumn < 5 then ',' else '' end
    + case when power(2, 5) & @stringBitmap = 0 then '' else col_05 end
    + case when power(2, 6) & @stringBitmap > 0
    and @firstColumn < 6 then ',' else '' end
    + case when power(2, 6) & @stringBitmap = 0 then '' else col_06 end
    + case when power(2, 7) & @stringBitmap > 0
    and @firstColumn < 7 then ',' else '' end
    + case when power(2, 7) & @stringBitmap = 0 then '' else col_07 end
    + case when power(2, 8) & @stringBitmap > 0
    and @firstColumn < 8 then ',' else '' end
    + case when power(2, 8) & @stringBitmap = 0 then '' else col_08 end
    + case when power(2, 9) & @stringBitmap > 0
    and @firstColumn < 9 then ',' else '' end
    + case when power(2, 9) & @stringBitmap = 0 then '' else col_09 end
    + case when power(2,10) & @stringBitmap > 0
    and @firstColumn < 10 then ',' else '' end
    + case when power(2,10) & @stringBitmap = 0 then '' else col_11 end
    + case when power(2,11) & @stringBitmap > 0
    and @firstColumn < 11 then ',' else '' end
    + case when power(2,11) & @stringBitmap = 0 then '' else col_11 end
    + case when power(2,12) & @stringBitmap > 0
    and @firstColumn < 12 then ',' else '' end
    + case when power(2,12) & @stringBitmap = 0 then '' else col_12 end
    + case when power(2,13) & @stringBitmap > 0
    and @firstColumn < 13 then ',' else '' end
    + case when power(2,13) & @stringBitmap = 0 then '' else col_13 end
    + case when power(2,14) & @stringBitmap > 0
    and @firstColumn < 14 then ',' else '' end
    + case when power(2,14) & @stringBitmap = 0 then '' else col_14 end
    + case when power(2,15) & @stringBitmap > 0
    and @firstColumn < 15 then ',' else '' end
    + case when power(2,15) & @stringBitmap = 0 then '' else col_15 end
    + case when power(2,16) & @stringBitmap > 0
    and @firstColumn < 16 then ',' else '' end
    + case when power(2,16) & @stringBitmap = 0 then '' else col_16 end
    + case when power(2,17) & @stringBitmap > 0
    and @firstColumn < 17 then ',' else '' end
    + case when power(2,17) & @stringBitmap = 0 then '' else col_17 end
    + case when power(2,18) & @stringBitmap > 0
    and @firstColumn < 18 then ',' else '' end
    + case when power(2,18) & @stringBitmap = 0 then '' else col_18 end
    + case when power(2,19) & @stringBitmap > 0
    and @firstColumn < 19 then ',' else '' end
    + case when power(2,19) & @stringBitmap = 0 then '' else col_19 end
    + case when power(2,20) & @stringBitmap > 0
    and @firstColumn < 20 then ',' else '' end
    + case when power(2,20) & @stringBitmap = 0 then '' else col_20 end
    + case when power(2,20) & @stringBitmap > 0
    and @firstColumn < 20 then ',' else '' end
    + case when power(2,20) & @stringBitmap = 0 then '' else col_20 end
    + case when power(2,21) & @stringBitmap > 0
    and @firstColumn < 21 then ',' else '' end
    + case when power(2,21) & @stringBitmap = 0 then '' else col_21 end
    + case when power(2,22) & @stringBitmap > 0
    and @firstColumn < 22 then ',' else '' end
    + case when power(2,22) & @stringBitmap = 0 then '' else col_22 end
    + case when power(2,23) & @stringBitmap > 0
    and @firstColumn < 23 then ',' else '' end
    + case when power(2,23) & @stringBitmap = 0 then '' else col_23 end
    + case when power(2,24) & @stringBitmap > 0
    and @firstColumn < 24 then ',' else '' end
    + case when power(2,24) & @stringBitmap = 0 then '' else col_24 end
    + case when power(2,25) & @stringBitmap > 0
    and @firstColumn < 25 then ',' else '' end
    + case when power(2,25) & @stringBitmap = 0 then '' else col_25 end
    + case when power(2,26) & @stringBitmap > 0
    and @firstColumn < 26 then ',' else '' end
    + case when power(2,26) & @stringBitmap = 0 then '' else col_26 end
    + case when power(2,27) & @stringBitmap > 0
    and @firstColumn < 27 then ',' else '' end
    + case when power(2,27) & @stringBitmap = 0 then '' else col_27 end
    + case when power(2,28) & @stringBitmap > 0
    and @firstColumn < 28 then ',' else '' end
    + case when power(2,28) & @stringBitmap = 0 then '' else col_28 end
    + case when power(2,29) & @stringBitmap > 0
    and @firstColumn < 29 then ',' else '' end
    + case when power(2,29) & @stringBitmap = 0 then '' else col_29 end
    + case when power(2,30) & @stringBitmap > 0
    and @firstColumn < 30 then ',' else '' end
    + case when power(2,30) & @stringBitmap = 0 then '' else col_30 end
    + '}'
    as results
    from mock

    print ' '
    select datediff (ms, @begDt, getdate()) as [Elapsed Time]


    -- ------------ Sample Output: ------------

    -- rid columnList results
    -- ----------- --------------- ---------------------------------
    -- 1 1,3,7,19,30 {(1,1),(1,3),(1,7),(1,19),(1,30)}
    -- 2 1,3,7,19,30 {(2,1),(2,3),(2,7),(2,19),(2,30)}
    -- 3 1,3,7,19,30 {(3,1),(3,3),(3,7),(3,19),(3,30)}
    -- 4 1,3,7,19,30 {(4,1),(4,3),(4,7),(4,19),(4,30)}
    -- 5 1,3,7,19,30 {(5,1),(5,3),(5,7),(5,19),(5,30)}

    -- Elapsed Time
    -- ------------
    -- 30

    set @begDt = getdate()


    -- -------------------------------------------------------------
    -- This method is based on use of the UNPIVOT operator. It is
    -- nowhere near as fast as the SELECT queries based on the
    -- CASE statements; however, it is more readable. Also, this
    -- code will look better than the other code should you expand
    -- the number of candidate columns from say 30 to 100.
    -- -------------------------------------------------------------
    select top 5
    a.rid,
    (
    select columnString + ',' as [text()]
    from ( select rid,
    convert (smallint, columnNo) as columnNo,
    columnString
    from vMockStringer
    unpivot ( columnString for columnNo in
    ( [1], [2], [3], [4], [5], Devil, [7], Music, [9],[10],
    [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
    [21],[22],[23],[24],[25],[26],[27],[28],[29],[30]
    )) unpiv
    ) x
    where columnNo >= @stringFirst
    and columnNo <= @stringLast
    and x.rid = a.rid
    order by columnNo
    for xml path ('')
    )
    from mock a

    print ' '
    select datediff (ms, @begDt, getdate()) as [Elapsed Time]


    -- rid
    -- ----------- -------------------------------------------------------------------------------------
    -- 1 (1,19),(1,20),(1,21),(1,22),(1,23),(1,24),(1,25),(1,26),(1,27),(1,28),(1,29),(1,30),
    -- 2 (2,19),(2,20),(2,21),(2,22),(2,23),(2,24),(2,25),(2,26),(2,27),(2,28),(2,29),(2,30),
    -- 3 (3,19),(3,20),(3,21),(3,22),(3,23),(3,24),(3,25),(3,26),(3,27),(3,28),(3,29),(3,30),
    -- 4 (4,19),(4,20),(4,21),(4,22),(4,23),(4,24),(4,25),(4,26),(4,27),(4,28),(4,29),(4,30),
    -- 5 (5,19),(5,20),(5,21),(5,22),(5,23),(5,24),(5,25),(5,26),(5,27),(5,28),(5,29),(5,30),


    -- Elapsed Time
    -- ------------
    -- 373
  • Creating a hierarchical view with recursion
Answer this question