T-SQL query to merge all rows into one row I have a T-SQL query, which pulls details from several locations (Something like below):

IF OBJECT_ID('tempdb..#mytable') IS NOT NULL DROP TABLE #mytable;

CREATE table #mytable
    ([Locno] varchar(6), [Stories] varchar(6), [Type] varchar(13))
;

INSERT INTO #mytable
    ([Locno], [Stories], [Type])
VALUES
    ('54', '2', 'TH'),
    ('65', '5', 'OB')
;

SELECT * FROM #mytable

Locno | Stories | Type
54    | 2       | TH
65    | 5       | OB


I want to bring all the rows into a single row. It's not a PIVOT or CONCATENATE, but something like below (I might have n number of locations): 

Locno_1 | Stories_1 | Type_1 | Locno_2 | Stories_2 | Type_2
    54  | 2         | TH     |  65     | 5         | OB


Can someone guide me how to do the same? 
TIA
For an arbitrary number of columns into a single row, you would have to build the dynamic SQL yourself.  There may be cleaner ways than this, but the below is a start point.  

Note that, unless the query optimiser can do something nice for you (which I doubt), it will scan the original data a huge number of times (at least 2 + colcount * rowcount):

DECLARE @query AS NVARCHAR(MAX);

SELECT @query = N'
    WITH r AS (SELECT _r = ROW_NUMBER() OVER (ORDER BY (SELECT 1)), * FROM #mytable)
    SELECT
';

;WITH 
 r AS (
    SELECT r = CONVERT(SYSNAME, ROW_NUMBER() OVER (ORDER BY (SELECT 1)))
    FROM #mytable
 )
,c AS (
    SELECT c = REPLACE(name, ']', ']]')
    FROM sys.dm_exec_describe_first_result_set('SELECT * FROM #mytable', '', 0)
 )
SELECT @query = @query + STUFF((SELECT N',[' + c + N'_' + r + N'] = (SELECT [' + c + N'] FROM r WHERE _r = ' + r + N')' + NCHAR(10)
                                FROM r, c
                                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 
                                1, 1, N'')

PRINT(@query);
EXECUTE sp_executesql @query;


This kind of thing is much-better handled in a presentation layer, rather than at the database layer.  C#/.NET could munch through an IDataReader and represent to a one-row HTML table far more reliably than SQL:

using (var conn = new SqlConnection("Data Source=.; Integrated Security=SSPI"))
using (var cmd = new SqlCommand("SELECT * FROM myRealTable;", conn))
using (var reader = cmd.ExecuteReader())
{
    var htmlTableHeader = new StringBuilder();
    var htmlTableRow = new StringBuilder();
    var rownum = 1L;

    while (reader.Read())
    {        
        for (var f = 0; f &lt; reader.FieldCount; f++)
        {
            htmlTableHeader.AppendFormat("&lt;th&gt;{0}_{1}&lt;/th&gt;", 
                                         WebUtility.HtmlEncode(reader.GetName(f)),
                                         rownum)
                           .AppendLine();

            htmlTableRow.AppendFormat("&lt;td&gt;{0}&lt;/td&gt;", 
                                      reader.IsDBNull(f) 
                                        ? "-"
                                        : WebUtility.HtmlEncode(reader.GetValue(f).ToString()))
                        .AppendLine();
        }
        ++rownum;
    }

    Console.WriteLine("&lt;table&gt;&lt;thead&gt;&lt;tr&gt;");
    Console.WriteLine(htmlTableHeader.ToString());
    Console.WriteLine("&lt;/tr&gt;&lt;/thead&gt;");
    Console.WriteLine("&lt;tbody&gt;&lt;tr&gt;");
    Console.WriteLine(htmlTableRow.ToString());
    Console.WriteLine("&lt;/tr&gt;&lt;tbody&gt;&lt;/table&gt;");
}