show single field across several fields in record set?

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    I have a table where I query one of the fields. my question is, is there a way I can produce the results across 3 fields? I'd like to use a datagrid on a webpage with 3 columns, and fill the datagrid with the record set starting at the first column with the first result, then the second column with the second result, then the third column with the third result, then the first column second row gets the fourth result and so on. I'm assuming I need to populate a tempory table which has 3 columns with the data from my table? but I'm not sure how.

    my table would be populated like;

    field1

    item1

    item2

    item3

    item4

    item5

    item6

    item7

    ...... and so on

    the desired record set as shown in my datagrid would look like

    Field1 Field2 Field3

    item1 item2 item3

    item4 item5 item6

    item7 ....... ........

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    I've worked it out and I've included it below, unless someone can show me a better way?

    CREATE TABLE #Results (RowID int identity(1,1), Col1 varchar(max),col2 varchar(max), col3 varchar(max))

    declare @counter as int

    declare @sCol1 Varchar(50)

    declare @sCol2 Varchar(50)

    declare @sCol3 Varchar(50)

    set @counter = 1

    declare @TmpName as varchar (max)

    DECLARE CUR1 CURSOR FAST_FORWARD FOR

    SELECT DISTINCT [CD Title]

    FROM [Club CDs].dbo.[Club CDs]

    ORDER BY [CD Title]

    OPEN CUR1

    FETCH NEXT FROM CUR1 INTO @TmpName

    set @sCol1 = @TmpName

    set @counter = @counter+1

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    FETCH NEXT FROM CUR1 INTO @TmpName

    print @TmpName

    if @counter = 1

    begin

    set @sCol1 = @TmpName

    end

    if @counter = 2

    begin

    set @sCol2 = @TmpName

    end

    if @counter = 3

    begin

    set @sCol3 = @TmpName

    set @counter = 0

    insert into #Results ([Col1],[col2],[col3]) values (@sCol1,@sCol2,@sCol3)

    end

    set @counter = @counter+1

    END

    CLOSE CUR1

    DEALLOCATE CUR1

    SELECT * FROM #Results

    drop table #Results

  • Rick Krueger

    SSC Eights!

    Points: 836

    Try not to use SQL Server's resources for this type of thing. Do it on the client side, something like this:

    //This assumes you have already populated a DataTable named

    //dataTableFromSQL, and the first column contains the values you want

    //clientSideTable is the DataTable that the DataGrid will be bound to

    DataTable clientSideTable = new DataTable();

    clientSideTable.Columns.Add(new DataColumn("Col1", typeof(string)));

    clientSideTable.Columns.Add(new DataColumn("Col2", typeof(string)));

    clientSideTable.Columns.Add(new DataColumn("Col3", typeof(string)));

    //initial newRow is used by the first row of values

    DataRow newRow = clientSideTable.NewRow();

    int columnIndex = 0;

    for (int rowIndex = 0; rowIndex <= dataTableFromSQL.Rows.Count - 1; rowIndex++) {

    string nextValue = dataTableFromSQL.Rows[rowIndex][0].ToString();

    newRow[columnIndex] = nextValue;

    columnIndex++;

    //If all 3 columns have been populated, add the row to the table and create a new row

    if ((rowIndex + 1) % 3 == 0) {

    clientSideTable.Rows.Add(newRow);

    newRow = clientSideTable.NewRow();

    columnIndex = 0;

    }

    }

    //We have exited the for loop, but the last row may not have been added to the table yet

    if (columnIndex > 0)

    clientSideTable.Rows.Add(newRow);



    Rick Krueger

    Follow @dataogre

  • Jeff Moden

    SSC Guru

    Points: 996851

    mick burden (3/9/2013)


    I've worked it out and I've included it below, unless someone can show me a better way?

    Use the natural loops (pseudo cursors) found in every SELECT to number the rows and then pivot the rows mathematically using a classic Cross Tab (my favorite) or a PIVOT function. Like this...

    --===== Create the test table and populate it on-the-fly.

    -- This is NOT a part of the solution.

    SELECT d.Field1

    INTO #Yourtable

    FROM (

    SELECT 'item1' UNION ALL

    SELECT 'item2' UNION ALL

    SELECT 'item3' UNION ALL

    SELECT 'item4' UNION ALL

    SELECT 'item5' UNION ALL

    SELECT 'item6' UNION ALL

    SELECT 'item7'

    )d(Field1)

    ;

    --===== Pivot the data with math instead of a While Loop

    WITH

    cteEnumerate AS

    (

    SELECT ItemNumber = ROW_NUMBER() OVER (ORDER BY Field1)-1,

    Field1

    FROM #YourTable

    ),

    ctePrePivot AS

    (

    SELECT RowNum = ItemNumber/3,

    ColNum = ItemNumber%3,

    Field1

    FROM cteEnumerate

    ) --=== Classic Cross Tab pivots the data mathematically

    SELECT Field1 = MAX(CASE WHEN ColNum = 0 THEN Field1 ELSE '' END),

    Field2 = MAX(CASE WHEN ColNum = 1 THEN Field1 ELSE '' END),

    Field3 = MAX(CASE WHEN ColNum = 2 THEN Field1 ELSE '' END)

    FROM ctePrePivot

    GROUP BY RowNum

    ORDER BY RowNum

    ;

    For more information on how Cross Tabs work, please see the following article.

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    thanks both of you, I give both a try and see what's easier, although I think I'd rather it was done inside a SP rather than clientside

  • Jeff Moden

    SSC Guru

    Points: 996851

    Rick Krueger (3/9/2013)


    Try not to use SQL Server's resources for this type of thing. Do it on the client side, something like this:

    I couldn't agree more. It's a great way to remove some of the load from the server.

    Just remember that not everything has a "client side" to it and when that happens, you'll need to know how to do it using as few resources and as much performance as possible on the server side.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply