asked on

SQL Select string together row data?

I need to build a string in SQL Server
It should be done in a UI but I'm not given that option and have been told to do it this way

So if I have a output table with two columns and x rows...and my output rows are

col1r1Data col2r1data
col1r2Data col2r2data
col1r3Data col2r3data

I can build this string

<td>col1r1Data </td><td>col2r1data</td><td>col1r2Data </td><td>col2r2data</td>...etc...

Jim Horn

Below is a hack job of T-SQL from a previous project, but it should get you where you need to go. Let me know if you like this, and I'll kick out an article on it.

Setup

CREATE TABLE your_table(Col1 int, Col2 int) 

INSERT INTO your_table(Col1, Col2) 
VALUES (5,5), (6,77), (9, 82), (101, 549)

Open in new window

The scalar function

ALTER FUNCTION [dbo].[ufn_string_together_row_data]() RETURNS varchar(8000) AS
begin

/*
Mighty profound code comments go here

02-21-13   jhorn15   Original
*/	 

-- TROUBLESHOOTING ONLY, set to 0 for production, 1 for extra troubleshooting sets. 
DECLARE @debug bit = 0

Declare @msg as varchar(8000)

-- Declare @cr varchar(1) = CHAR(10), @tab varchar(1) = CHAR(9)  -- Plain Text
Declare @cr varchar(4) = '<br>', @tab varchar(5) = '<tab>'


DECLARE @col1 bigint, @col2 bigint
DECLARE @col1_c varchar(100), @col2_c varchar(100)


/*
IF @debug = 1
	SELECT @start_dt AS start_dt, @success AS success FROM LOG_EXECUTIONS WHERE id = @id
*/

-- Message header 
SELECT @msg = 'A really cool sounding title line goes here.' + @cr


-- Get the total rows inserted/updated/etc. as a bigint. 
SELECT 
	@col1 = SUM(COALESCE(Col1,0)), 
	@col2 = SUM(COALESCE(Col2, 0))
FROM your_table

-- Get the above as a varchar. 
SELECT 
	@col1_c = COALESCE(@Col1, 0),
	@col2_c = COALESCE(@Col2, 0)
FROM your_table

/*
IF @debug = 1
	begin
	SELECT 'Variables', @col1 as Col1, @col2 AS Col2
	SELECT @msg
	end
*/
-- Details
SELECT @msg = @msg + @cr + '*** Details ***' + @cr

-- HEADER
SELECT @msg = @msg + '<table border="2" cellspacing="0" cellpadding="1">'
SELECT @msg = @msg + '<tr><th>Col1 Header</th><th>Col2 Header</th>'

-- TOTALS ROW
SELECT @msg = @msg +
	'<tr>' +
	--'<td></td>' +
	'<td>TOTAL</td>' +
	'<td>' + @col1_c + '</td>' +
  	'<td>' + @col2_c + '</td>' +  
	'</tr>'


-- DETAIL ROWS
DECLARE cur CURSOR FOR 
SELECT Col1, Col2
FROM your_table

OPEN cur

FETCH NEXT FROM cur 
INTO @col1, @col2

WHILE @@FETCH_STATUS = 0
	BEGIN

	SELECT @msg = @msg + '<tr><td align="center" colspan="8"><b>Section Title</b></td></tr>'
	SELECT @msg = @msg +
		'<tr>' + 
		'<td>' + @col1_c + '</td>' +
		'<td>' + @col2_c + '</td>' +
		'</tr>' 

    FETCH NEXT FROM cur 
    INTO @col1, @col2
    
	END 
CLOSE cur
DEALLOCATE cur

SELECT @msg = @msg + '</table>'
RETURN @msg

end

Open in new window

Then call it like this

SELECT dbo.ufn_string_together_row_data()

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Larry Brister

ASKER

Extremely elegant

Larry Brister

ASKER

ScottPletcher
Watch for followup question