Using gridview to display string data vertically in a row

Question

I am trying to find out is there a way to take the string data in the Name, Address, and DSL columns that has been concatenated in STUFF() and XML PATH and display it vertically in same row instead of horizontally on the front end. I am using a gridview to display the information.

  SELECT  i.[InstrumentID], it.[InstrumentType],i.[InstrumentNumber], i.[NANumber],i.[DateTimeFiled],
    STUFF((SELECT ',   ' + n.[Surname] + n.[GivenName] + '(' + nc.[NameCode] + ')'  FROM [dbo].[tblName] n 
INNER JOIN [dbo].[tblNameCode] nc ON nc.[NameCodeID] = n.[NameCodeID] Where n.[InstrumentID] = i.[InstrumentID] ORDER BY  n.[Surname] FOR XML PATH (''), TYPE).value('.', 'varchar(max)'), 1, 1, '') AS Name ,
    STUFF(( SELECT ',' + a.[StreetNumber] + a.[StreetName] FROM [dbo].[tblAddress] a Where a.[InstrumentID] = i.[InstrumentID] ORDER BY  a.[StreetName] FOR XML PATH (''), TYPE).value('.', 'varchar(max)'), 1, 1, '') AS Address,
    STUFF(( SELECT ''  + d.[District] + ' ' +d.[Square]+ ' ' + d.[Lot] + ', ' FROM [dbo].[tblDLS] d WHERE d.[InstrumentID] = i.[InstrumentID] FOR XML PATH (''), TYPE).value('.', 'varchar(max)'), 1, 1,'') AS DSL
FROM [dbo].[tblInstrument] i 
INNER JOIN [dbo].[tblInstrumentType] it ON i.[InstrumentTypeID] = it.[InstrumentTypeID] 
WHERE InstrumentNumber = 1
ORDER BY InstrumentNumber, InstrumentType 

Here is my output

There is nothing wrong as far as getting results. The issue is getting the data Vertical. Here is a screenshot of my program also:

SamplePage

Here is the aspx code for displaying the info:

<Columns>
 <asp:TemplateField HeaderText="Instrument #, Type, Dist., Squ. , Lot">
                       <ItemTemplate>
                            <asp:LinkButton ID="LbPath" runat="server" 
                               Text='<%# String.Format("{0} {1}", Eval("InstrumentNumber"), Eval("InstrumentType")) %>' 
                                CommandName="GetInstrument" 
                                CommandArgument='<%#Bind("instrumentID") %>'>
                            </asp:LinkButton>
                           <br />
                           <asp:Label ID="lblDateFiled" runat="server" Text='<%# Eval("DateTimeFiled") %>'> </asp:Label>
                             <br />
                            <asp:Label ID="lblNANumber" runat="server" Font-Names="Segoe UI Semibold" Font-Size="Medium" Text="NA Number: " ForeColor="#434343"></asp:Label><%# Eval("NANumber") %>
                           <br />
                           <br />
                           <asp:Label ID="lblDSL" runat="server" Text='<%#Eval("DSL") %>'> </asp:Label> 
                            <%--<asp:Label ID="lblDSL" runat="server" Text='<%# String.Format("{0} {1} {2}", Eval("District"), Eval("Square"),  Eval("Lot")) %>'> </asp:Label> --%>                           
                        </ItemTemplate>
                   </asp:TemplateField>
                        <asp:TemplateField HeaderText="Name, Address" SortExpression="Surname">
                <ItemTemplate>
                    <EditItemTemplate>
                        
                   <%-- <asp:Label ID="label1"  runat="server" Text='<%# Bind("instrumentID") %>' ></asp:Label>--%>
                </EditItemTemplate>
                                    <asp:Label ID="lblName" runat="server" Text='<%#  Eval("Name") %>'></asp:Label>
<%--                    <asp:Label ID="lblName" runat="server" Text='<%# String.Format(" {0}, {1} ({2})", Eval("Surname"), Eval("GivenName"), Eval("Namecode")) %>'></asp:Label>--%>
                    <br />
                    <asp:Label ID="lblAddress" runat="server" Font-Names="Segoe UI Semibold" Font-Size="Medium" Text='<%# Eval("Address") %>'></asp:Label>
                   <%--<asp:Label ID="lblAddress" runat="server" Font-Names="Segoe UI Semibold" Font-Size="Medium" Text='<%# String.Format("{0} {1} {2}", Eval("Subdivision"), Eval("StreetNumber"), Eval("StreetName")) %>'></asp:Label>--%>
                </ItemTemplate>
            </asp:TemplateField>
                  <%-- <asp:BoundField DataField="InstrumentType" HeaderText="Instrument Type" InsertVisible="false" ReadOnly="true" SortExpression="InstrumentType" />
                   <asp:BoundField DataField="NANumber" HeaderText="NANumber" InsertVisible="false" SortExpression="NANumber" />
                   <asp:BoundField DataField="DateTimeFiled" HeaderText="DateTimeFiled" InsertVisible="false" ReadOnly="true" SortExpression="DateTimeFiled" />--%>

               </Columns>

Answer 1

Assuming I understand your need, you could try something like the following:

/* Create a mock-up table with sample data */

DECLARE @Data TABLE (
    InstrumentID INT, InstrumentType VARCHAR(50), InstrumentNumber INT, NANumber INT, DateTimeFiled DATETIME, [Name] VARCHAR(255) 
);

INSERT INTO @Data ( InstrumentID, InstrumentType, InstrumentNumber, NANumber, DateTimeFiled, [Name] )
VALUES
    ( 1625168, 'ACCOUNTS RECEIVABLE', 1, 785401, '1989-01-03 09:48:03.000', 'HIBERNIA NATIONAL BANK NEW ORLEANS' ),
    ( 1382385, 'SALE', 1, 785467, '1989-01-03 10:14:03.000', 'M & M RESTAURANT AND LOUNGE, INC' );

/* "Pivot" the columnar data */
SELECT
    x.f.value( 'fn:local-name(.)', 'VARCHAR(50)' ) AS [name],
    x.f.value( '.', 'VARCHAR(255)' ) AS [value]
FROM (
    
    /* Insert your T-SQL here... */
    SELECT CAST ( ( SELECT
        InstrumentID, InstrumentType, InstrumentNumber, NANumber, DateTimeFiled, [Name]
    FROM @Data
    FOR XML PATH( 'Data' ) ) AS XML ) AS DataXml

) AS d
CROSS APPLY d.DataXml.nodes( '//Data/*' ) x( f );

Returns

+------------------+------------------------------------+
|       name       |               value                |
+------------------+------------------------------------+
| InstrumentID     | 1625168                            |
| InstrumentType   | ACCOUNTS RECEIVABLE                |
| InstrumentNumber | 1                                  |
| NANumber         | 785401                             |
| DateTimeFiled    | 1989-01-03T09:48:03                |
| Name             | HIBERNIA NATIONAL BANK NEW ORLEANS |
| InstrumentID     | 1382385                            |
| InstrumentType   | SALE                               |
| InstrumentNumber | 1                                  |
| NANumber         | 785467                             |
| DateTimeFiled    | 1989-01-03T10:14:03                |
| Name             | M & M RESTAURANT AND LOUNGE, INC   |
+------------------+------------------------------------+