Professional Web Applications Themes

A complex query - Microsoft SQL / MS SQL Server

hi folks, I have a table like this.... Col1 Col2 Col3 Col4 This NULL NULL NULL NULL is NULL NULL NULL NULL line NULL NULL NULL NULL one That NULL NULL NULL NULL Will NULL NULL NULL NULL be NULL NULL NULL NULL two I need to get the results as below....actually I need to select diagonal elements into one row. This is line one That will be two Is there any way to do it??? Any ideas.... Thanks in advance....

  1. #1

    Default A complex query

    hi folks,
    I have a table like this....

    Col1
    Col2
    Col3
    Col4

    This
    NULL
    NULL
    NULL

    NULL
    is
    NULL
    NULL

    NULL
    NULL
    line
    NULL

    NULL
    NULL
    NULL
    one

    That
    NULL
    NULL
    NULL

    NULL
    Will
    NULL
    NULL

    NULL
    NULL
    be
    NULL

    NULL
    NULL
    NULL
    two



    I need to get the results as below....actually I need to select diagonal elements into one row.

    This
    is
    line
    one

    That
    will
    be
    two



    Is there any way to do it???
    Any ideas....
    Thanks in advance.
    Venkatesan M Guest

  2. #2

    Default Re: A complex query

    If you have an additional column which will identify these 4 rows as a part of one family you can try following query.

    drop table test1
    create table test1 (idd int,col1 varchar(50), col2 varchar(50), col3 varchar(50), col4 varchar(50))

    insert into test1
    select 1,'This', NULL, NULL, NULL union all
    select 1, NULL, 'is', NULL, NULL union all
    select 1, NULL, NULL, 'line', NULL union all
    select 1, NULL, NULL, NULL, 'one'

    insert into test1
    select 2,'That', NULL, NULL, NULL union all
    select 2, NULL, 'Will', NULL, NULL union all
    select 2, NULL, NULL, 'be', NULL union all
    select 2, NULL, NULL, NULL, 'two'

    SELECT IDD, MAX(ISNULL(COL1 ,'')) + ' ' + MAX(ISNULL(COL2 ,''))+ ' '+ MAX(ISNULL(COL3 ,'')) + ' ' + MAX(ISNULL(COL4 ,''))
    FROM TEST1
    GROUP BY IDD

    --
    -Vishal
    "Venkatesan M" <venkat_asphotmail.com> wrote in message news:eOLck08PDHA.1584TK2MSFTNGP11.phx.gbl...
    hi folks,
    I have a table like this....

    Col1
    Col2
    Col3
    Col4

    This
    NULL
    NULL
    NULL

    NULL
    is
    NULL
    NULL

    NULL
    NULL
    line
    NULL

    NULL
    NULL
    NULL
    one

    That
    NULL
    NULL
    NULL

    NULL
    Will
    NULL
    NULL

    NULL
    NULL
    be
    NULL

    NULL
    NULL
    NULL
    two



    I need to get the results as below....actually I need to select diagonal elements into one row.

    This
    is
    line
    one

    That
    will
    be
    two



    Is there any way to do it???
    Any ideas....
    Thanks in advance.
    Vishal Parkar Guest

  3. #3

    Default Re: A complex query

    Thanks Dean, but i need to do it without having another field.

    "Dean Savovic" <dsavovicinet.hr> wrote in message news:bds0ek$6hs$1brown.net4u.hr...
    Query is not complex. You are missing one important information: how can you determine what words are from
    which sentence?

    You need to add column (say: sentence) that will keep that information:


    create table test
    (sentence int
    , col1 char(10)
    , col2 char(10)
    , col3 char(10)
    , col4 char(10)
    )

    insert into test values (1, 'This', null, null, null)
    insert into test values (1, null, 'is', null, null)
    insert into test values (1, null, null, 'line', null)
    insert into test values (1, null, null, null, 'one')
    insert into test values (2, 'That', null, null, null)
    insert into test values (2, null, 'will', null, null)
    insert into test values (2, null, null, 'be', null)
    insert into test values (2, null, null, null, 'two')

    select
    sentence, col1 = max(col1), col2 = max(col2), col3 = max(col3), col4 = max(col4)
    from test
    group by sentence

    drop table test

    --
    Dean Savovic
    [url]www.teched.hr[/url]


    "Venkatesan M" <venkat_asphotmail.com> wrote in message news:eOLck08PDHA.1584TK2MSFTNGP11.phx.gbl...
    hi folks,
    I have a table like this....

    Col1
    Col2
    Col3
    Col4

    This
    NULL
    NULL
    NULL

    NULL
    is
    NULL
    NULL

    NULL
    NULL
    line
    NULL

    NULL
    NULL
    NULL
    one

    That
    NULL
    NULL
    NULL

    NULL
    Will
    NULL
    NULL

    NULL
    NULL
    be
    NULL

    NULL
    NULL
    NULL
    two



    I need to get the results as below....actually I need to select diagonal elements into one row.

    This
    is
    line
    one

    That
    will
    be
    two



    Is there any way to do it???
    Any ideas....
    Thanks in advance.
    Venkatesan M Guest

  4. #4

    Default Re: A complex query

    Thanks Vishal, but i need to do it without having another field.
    "Vishal Parkar" <vgparkarhotmail.com> wrote in message news:eIH4vD9PDHA.2228tk2msftngp13.phx.gbl...
    If you have an additional column which will identify these 4 rows as a part of one family you can try following query.

    drop table test1
    create table test1 (idd int,col1 varchar(50), col2 varchar(50), col3 varchar(50), col4 varchar(50))

    insert into test1
    select 1,'This', NULL, NULL, NULL union all
    select 1, NULL, 'is', NULL, NULL union all
    select 1, NULL, NULL, 'line', NULL union all
    select 1, NULL, NULL, NULL, 'one'

    insert into test1
    select 2,'That', NULL, NULL, NULL union all
    select 2, NULL, 'Will', NULL, NULL union all
    select 2, NULL, NULL, 'be', NULL union all
    select 2, NULL, NULL, NULL, 'two'

    SELECT IDD, MAX(ISNULL(COL1 ,'')) + ' ' + MAX(ISNULL(COL2 ,''))+ ' '+ MAX(ISNULL(COL3 ,'')) + ' ' + MAX(ISNULL(COL4 ,''))
    FROM TEST1
    GROUP BY IDD

    --
    -Vishal
    "Venkatesan M" <venkat_asphotmail.com> wrote in message news:eOLck08PDHA.1584TK2MSFTNGP11.phx.gbl...
    hi folks,
    I have a table like this....

    Col1
    Col2
    Col3
    Col4

    This
    NULL
    NULL
    NULL

    NULL
    is
    NULL
    NULL

    NULL
    NULL
    line
    NULL

    NULL
    NULL
    NULL
    one

    That
    NULL
    NULL
    NULL

    NULL
    Will
    NULL
    NULL

    NULL
    NULL
    be
    NULL

    NULL
    NULL
    NULL
    two



    I need to get the results as below....actually I need to select diagonal elements into one row.

    This
    is
    line
    one

    That
    will
    be
    two



    Is there any way to do it???
    Any ideas....
    Thanks in advance.
    Venkatesan M Guest

  5. #5

    Default Re: A complex query

    Vishal Parkar has already posted so I will not repeat his words.

    If you use temp table with identity row as Jacco posted ordering of inserted rows in temp table is not guaranteed to be as you need them to be, e.g. so you get correct sentences.

    As Vishal said: you need to change your database design. This way you simply don't know what words to put in which sentences!

    --
    Dean Savovic
    [url]www.teched.hr[/url]


    "Venkatesan M" <venkat_asphotmail.com> wrote in message news:uU8R9h9PDHA.704tk2msftngp13.phx.gbl...
    Thanks Dean, but i need to do it without having another field.

    "Dean Savovic" <dsavovicinet.hr> wrote in message news:bds0ek$6hs$1brown.net4u.hr...
    Query is not complex. You are missing one important information: how can you determine what words are from
    which sentence?

    You need to add column (say: sentence) that will keep that information:


    create table test
    (sentence int
    , col1 char(10)
    , col2 char(10)
    , col3 char(10)
    , col4 char(10)
    )

    insert into test values (1, 'This', null, null, null)
    insert into test values (1, null, 'is', null, null)
    insert into test values (1, null, null, 'line', null)
    insert into test values (1, null, null, null, 'one')
    insert into test values (2, 'That', null, null, null)
    insert into test values (2, null, 'will', null, null)
    insert into test values (2, null, null, 'be', null)
    insert into test values (2, null, null, null, 'two')

    select
    sentence, col1 = max(col1), col2 = max(col2), col3 = max(col3), col4 = max(col4)
    from test
    group by sentence

    drop table test

    --
    Dean Savovic
    [url]www.teched.hr[/url]


    "Venkatesan M" <venkat_asphotmail.com> wrote in message news:eOLck08PDHA.1584TK2MSFTNGP11.phx.gbl...
    hi folks,
    I have a table like this....

    Col1
    Col2
    Col3
    Col4

    This
    NULL
    NULL
    NULL

    NULL
    is
    NULL
    NULL

    NULL
    NULL
    line
    NULL

    NULL
    NULL
    NULL
    one

    That
    NULL
    NULL
    NULL

    NULL
    Will
    NULL
    NULL

    NULL
    NULL
    be
    NULL

    NULL
    NULL
    NULL
    two



    I need to get the results as below....actually I need to select diagonal elements into one row.

    This
    is
    line
    one

    That
    will
    be
    two



    Is there any way to do it???
    Any ideas....
    Thanks in advance.
    Dean Savovic Guest

  6. #6

    Default Re: A complex query


    Hers is the problem:
    > ... actually I need to select diagonal elements into one row.
    and
    > ... but i need to do it without having another field.
    Rows in a table do not have a sequence.
    Without a row sequence to go with the "column sequence",
    there are no particular diagonals.
    Thus you have lost information (assuming the information
    existed to be lost per your description).

    So you either need an additional column to retain the row sequence info,
    or you could get all possible "diagonals" as show below.
    I use the old style syntax to make the example clearer.
    It would be good to switch to the newer join
    syntax, so you can better specify what you want
    if you start wanting to do outer joins.


    Borrowing from Dean:

    create table test
    (
    col1 char(10)
    , col2 char(10)
    , col3 char(10)
    , col4 char(10)
    )

    insert into test values ('This', null, null, null)
    insert into test values (null, 'is', null, null)
    insert into test values (null, null, 'line', null)
    insert into test values (null, null, null, 'one')
    insert into test values ('That', null, null, null)
    insert into test values (null, 'will', null, null)
    insert into test values (null, null, 'be', null)
    insert into test values (null, null, null, 'two')

    select t1.col1, t2.col2, t3.col3, t4.col4
    from test t1, test t2, test t3, test t4
    where t1.col1 is not null
    and t2.col2 is not null
    and t3.col3 is not null
    and t4.col4 is not null
    order by t1.col1, t2.col2, t3.col3, t4.col4

    Bye,
    Delbert Glass

    "Venkatesan M" <venkat_asphotmail.com> wrote in message news:uU8R9h9PDHA.704tk2msftngp13.phx.gbl...
    Thanks Dean, but i need to do it without having another field.

    "Dean Savovic" <dsavovicinet.hr> wrote in message news:bds0ek$6hs$1brown.net4u.hr...
    Query is not complex. You are missing one important information: how can you determine what words are from
    which sentence?

    You need to add column (say: sentence) that will keep that information:


    create table test
    (sentence int
    , col1 char(10)
    , col2 char(10)
    , col3 char(10)
    , col4 char(10)
    )

    insert into test values (1, 'This', null, null, null)
    insert into test values (1, null, 'is', null, null)
    insert into test values (1, null, null, 'line', null)
    insert into test values (1, null, null, null, 'one')
    insert into test values (2, 'That', null, null, null)
    insert into test values (2, null, 'will', null, null)
    insert into test values (2, null, null, 'be', null)
    insert into test values (2, null, null, null, 'two')

    select
    sentence, col1 = max(col1), col2 = max(col2), col3 = max(col3), col4 = max(col4)
    from test
    group by sentence

    drop table test

    --
    Dean Savovic
    [url]www.teched.hr[/url]


    "Venkatesan M" <venkat_asphotmail.com> wrote in message news:eOLck08PDHA.1584TK2MSFTNGP11.phx.gbl...
    hi folks,
    I have a table like this....

    Col1
    Col2
    Col3
    Col4

    This
    NULL
    NULL
    NULL

    NULL
    is
    NULL
    NULL

    NULL
    NULL
    line
    NULL

    NULL
    NULL
    NULL
    one

    That
    NULL
    NULL
    NULL

    NULL
    Will
    NULL
    NULL

    NULL
    NULL
    be
    NULL

    NULL
    NULL
    NULL
    two



    I need to get the results as below....actually I need to select diagonal elements into one row.

    This
    is
    line
    one

    That
    will
    be
    two



    Is there any way to do it???
    Any ideas....
    Thanks in advance.
    Delbert Glass Guest

Similar Threads

  1. Help! Complex query
    By RuBot in forum Coldfusion Database Access
    Replies: 5
    Last Post: March 7th, 12:47 AM
  2. Help for quite complex query.
    By _andrea.l in forum MySQL
    Replies: 3
    Last Post: January 1st, 04:22 PM
  3. Presenting complex query results through datagrid
    By Bijoy Naick in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: February 24th, 08:19 PM
  4. Adding/Updating records on form with complex query
    By Steve Marsden in forum Microsoft Access
    Replies: 0
    Last Post: July 28th, 10:07 AM
  5. Fairly Complex Query
    By Mike Davies in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 1st, 06:19 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139