Show multiple rows in already defined columns of gridview

Question

I have the following gridview:

<asp:GridView ID="gvTimeline" ShowHeader="true"  runat="server" ShowHeaderWhenEmpty="true" AutoGenerateColumns="false" >
<Columns>
         <asp:BoundField DataField="8" ItemStyle-Width="25%" DataFormatString="{0:dd-MM-yyyy}" />
         <asp:BoundField DataField="2" ItemStyle-Width="25%" DataFormatString="{0:dd-MM-yyyy}" />
         <asp:BoundField DataField="6" ItemStyle-Width="25%" DataFormatString="{0:dd-MM-yyyy}" />
         <asp:BoundField DataField="4" ItemStyle-Width="25%" DataFormatString="{0:dd-MM-yyyy}" />
</Columns>
</asp:GridView>

And the following sql table:

Id   |  type  |  date

2    |    8   |  22/09/1997   
1    |    6   |  22/09/1998   
3    |    4   |  22/09/1999

What I want is to run the sql rows and go filling each column with the date corresponding to the type (only 1 row). Each datafield is a type. What is the best way to do this, later getting access to each id normally?

I expected this:

8                 2                 6              4
22/09/1997      null          22/09/1998       22/09/1999

It appears you’re trying to derive a new table from the *values* of an existing table, and bind to _that_ tables columns, right? — Frank Alvaro, Jun 26 '19 at 12:30

Frank Alvaro · Answer 1 · 2019-06-27T16:35:55.227

If you're limiting to just those values, then creating a simple view and binding to it might be the simplest way to go. For example:

CREATE VIEW view_for_gridview_binding AS
  SELECT DISTINCT
    eight.[date] as '8'
    , two.[date] as '2'
    , six.[date] as '6'
    , four.[date] as '4'
  FROM table_for_gridview_question 
  LEFT JOIN table_for_gridview_question AS eight ON (eight.[type] = 8)
  LEFT JOIN table_for_gridview_question AS two ON (two.[type] = 2)
  LEFT JOIN table_for_gridview_question AS six ON (six.[type] = 6)
  LEFT JOIN table_for_gridview_question AS four ON (four.[type] = 4)
GO

Here's test:

CREATE TABLE table_for_gridview_question (
    Id int NOT NULL,
    [type] int NOT NULL,
    [date] date NOT NULL
)
GO

INSERT INTO table_for_gridview_question
SELECT 2, 8, '1997-09-22'
UNION
SELECT 1, 6, '1998-09-22'
UNION
SELECT 3, 4, '1999-09-22'
GO

CREATE VIEW view_for_gridview_binding AS
    SELECT DISTINCT
        eight.[date] as '8'
        , two.[date] as '2'
        , six.[date] as '6'
        , four.[date] as '4'
    FROM table_for_gridview_question 
    LEFT JOIN table_for_gridview_question AS eight ON (eight.[type] = 8)
    LEFT JOIN table_for_gridview_question AS two ON (two.[type] = 2)
    LEFT JOIN table_for_gridview_question AS six ON (six.[type] = 6)
    LEFT JOIN table_for_gridview_question AS four ON (four.[type] = 4)
GO

SELECT * FROM view_for_gridview_binding

DROP VIEW view_for_gridview_binding
DROP TABLE table_for_gridview_question

Results of SELECT:

8          2          6          4
---------- ---------- ---------- ----------
1997-09-22 NULL       1998-09-22 1999-09-22

If, however, your types can be anything, you may want to consider reading up on PIVOT and UNPIVOT like what's described in the Dynamic Pivot Version part of the answer here.

Show multiple rows in already defined columns of gridview

1 Answers1