How to read multiple rows of data from a table into a single asp.Label

Question

I am making a reporting tool with an aspx webpage that reads data from SQL tables and inserts that data into the text of a label. So far that's been easy. The question I have is, how can I read multiple rows of data from a table into a single asp.Label

For example,

Impression: Row1; Row2: Row3; Row4; Row5.

SQL Query

SELECT a.Value 
From ImpressionEcho a 
Inner join PatientData p on a.P_ID p.P_ID where p.P_ID = 1

Gives this data

Mildly dilated aortic root
Moderately dilated aortic root
Possible dissection
Mild mitral regurgitation
Reduced LV diastolic compliance
No significant valvular abnormalities

ASPX code for reader

<Table>
 <tr>
 <td colspan="2">
  <asp:Label ID="Aorta" runat="server" Text="Impression:" ForeColor="Black" Font-Underline="true"></asp:Label>
 </td>
 <td>
  <asp:Label ID="ImpressionReader" runat="server" Text="AortaReader" ForeColor="Black" ></asp:Label>
 </td>
 </tr>
</Table>

C# Code to call reader

 ImpressionReader.Text = reader["Value"].ToString();

I have been searching around and have not found anything to specific to my question so if anyone could help or point me in the right direction it is greatly appreciated.

UPDATE

Eli's answer pointed me in the right direction for those looking for the answer to this in the future this is the SQL query used

SELECT  DISTINCT
    STUFF((SELECT '; ' + Value
    FROM ImpressionEcho data1
    FOR XML PATH('')), 1, 1, '') [Impression]
    FROM ImpressionEcho data

Which gave me

 Mildly dilated aortic root; Moderately dilated aortic root; Possible dissection; Mild mitral regurgitation; Reduced LV diastolic compliance; No significant valvular abnormalities

Then I was able to read ["Impression"] from my C# reader

Can you show your code! – Salah Akbari May 25 '17 at 17:53 — Salah Akbari, May 25 '17 at 17:53
@S.Akbari Edits have been made – NMatheny May 25 '17 at 18:15 — NMatheny, May 25 '17 at 18:15

score 0 · Accepted Answer · answered May 25 '17 at 17:54

0

Use FOR XML to put all of the rows of data into the format which you show. If you would post data samples and what your table structure look like, we'd be able to help you faster

See this answer for a similar application to yours: Make a query that show result in another column

answered May 25 '17 at 17:54

Eli

2,538
1
25
36

great find! I added the query syntax from that link and it did the trick! – NMatheny May 25 '17 at 18:25

How to read multiple rows of data from a table into a single asp.Label

1 Answers1