Professional Web Applications Themes

grouping the columns - Microsoft SQL / MS SQL Server

Hi, Please help me out in the following. I hv a a table as below. COL1 COL2 COL3 COL4 123 x y z 123 A B C 123 E F G 124 H I J 124 K L M I need output in the following format. 123 x/A/E Y/B/F Z/C/G 124 H/K I/L J/M...

  1. #1

    Default grouping the columns

    Hi,

    Please help me out in the following.

    I hv a a table as below.

    COL1 COL2 COL3 COL4

    123 x y z
    123 A B C
    123 E F G
    124 H I J
    124 K L M

    I need output in the following format.

    123 x/A/E Y/B/F Z/C/G

    124 H/K I/L J/M
    raja Guest

  2. #2

    Default Re: grouping the columns

    If you are running on SQL Server 2000 then use function to produce output for each col1:




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

    go

    create function test_func(col1 int, colsel int)
    returns varchar(100)
    as
    begin
    declare str varchar(100)
    select str = ''
    select str = str + rtrim((case colsel
    when 2 then col2
    when 3 then col3
    when 4 then col4
    else 'err'
    end)) + '/'
    from test where col1 = col1
    return left(str, len(str) - 1)
    end

    go

    insert into test
    select 123, 'x', 'y', 'z'
    union all
    select 123, 'A', 'B', 'C'
    union all
    select 123, 'E', 'F', 'G'
    union all
    select 124, 'H', 'I', 'J'
    union all
    select 124, 'K', 'L', 'M'

    select
    col1, dbo.test_func(col1, 2), dbo.test_func(col1, 3), dbo.test_func(col1, 4)
    from test
    group by col1

    drop function test_func
    drop table test




    "raja" <com> wrote in message news:07ff01c355a8$91530670$gbl... 


    Dean Guest

  3. #3

    Default Re: grouping the columns

    If you want the values to be grouped in a specific sequence then you need a
    column that defines that sequence. Without that you can just sort them in
    alpha order like this:

    CREATE TABLE Sometable (col1 INTEGER, col2 CHAR(1), col3 CHAR(1), col4
    CHAR(1), PRIMARY KEY (col1, col2, col3, col4))

    INSERT INTO Sometable VALUES (123, 'x', 'y', 'z')
    INSERT INTO Sometable VALUES (123, 'A', 'B', 'C')
    INSERT INTO Sometable VALUES (123, 'E', 'F', 'G')
    INSERT INTO Sometable VALUES (124, 'H', 'I', 'J')
    INSERT INTO Sometable VALUES (124, 'K', 'L', 'M')

    SELECT col1,
    MIN(CASE seq WHEN 1 THEN col2 END)+
    COALESCE('/'+MIN(CASE seq WHEN 2 THEN col2 END),'')+
    COALESCE('/'+MIN(CASE seq WHEN 3 THEN col2 END),'') AS col2,
    MIN(CASE seq WHEN 1 THEN col3 END)+
    COALESCE('/'+MIN(CASE seq WHEN 2 THEN col3 END),'')+
    COALESCE('/'+MIN(CASE seq WHEN 3 THEN col3 END),'') AS col3,
    MIN(CASE seq WHEN 1 THEN col4 END)+
    COALESCE('/'+MIN(CASE seq WHEN 2 THEN col4 END),'')+
    COALESCE('/'+MIN(CASE seq WHEN 3 THEN col4 END),'') AS col4
    FROM
    (SELECT S1.col1, S1.col2, S1.col3, S1.col4,
    COUNT(*) AS seq
    FROM Sometable AS S1
    JOIN Sometable AS S2
    ON S1.col1=S2.col1 AND
    (S2.col2+S2.col3+S2.col4)
    <=(S1.col2+S1.col3+S1.col4)
    GROUP BY S1.col1, S1.col2, S1.col3, S1.col4) AS X
    GROUP BY col1

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

Similar Threads

  1. help with grouping
    By rere in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: July 21st, 08:41 PM
  2. Columns and Inherited Datagrid...Active Schema does not support columns
    By rob thomson in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: September 4th, 03:09 PM
  3. Grouping
    By SRam in forum Informix
    Replies: 2
    Last Post: August 28th, 02:54 PM
  4. Replies: 0
    Last Post: July 2nd, 06:18 AM

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