This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:
DECLARE @x TABLE
(
id INT,
row_num INT,
customer_code VARCHAR(32),
comments VARCHAR(32)
);
INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';
SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments
FROM @x AS x2 WHERE id = x.id
ORDER BY row_num
FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;
If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:
FOR XML PATH('')), 1, 1, '')
To this more elaborate approach:
FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')
(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)
It is unclear to me what is a "more elegant way".
Oracle you can use the following statement to make columns to rows
select all_name
from foo
unpivot (all_name for col_name in (
his_name,
her_name,
other_name));
This is the syntax diagram of the select statement
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
Neither the WHERE,GROUP BY, HAVING, LIMIT, SELECT, INTO, FOR UPDATE nor the LOCK IN SHARE MODE clause can increase the number of rows defined by the FROM clause.
So if table_references equals foo the query cannot contains more rows than the table foo.
So MySQL does not have such an "elegant" way to unpivot a table.
A way to do such unpivoting without the use of UNION can be done buy using a join. We want to create 3 rows for each row of the foo table, so we create an auxiliary table containing three rows
and (cross) join it to the foo table. Now we have three rows in our query for each row in the base table foo. Each query row can be filled by the appropriate data. Instead the ELT function one can use IF or CASE.
SQL Fiddle
MySQL 5.6 Schema Setup:
create table foo (
his_name varchar(10),
her_name varchar(10),
other_name varchar(10));
insert into foo(his_name,her_name,other_name) values ('one','two','three');
insert into foo(his_name,her_name,other_name) values ('four','five','six');
create table aux(line int);
insert into aux(line) values(1);
insert into aux(line) values(2);
insert into aux(line) values(3);
Pivot Query:
select elt(aux.line,foo.his_name,foo.her_name,foo.other_name) all_name
from foo cross join aux
Results:
| all_name |
|----------|
| one |
| four |
| two |
| five |
| three |
| six |
Of course there are different ways to create a table containing the three rows with values 1,2,3:
SQL Fiddle
Using an auxiliary table:
create table aux(line int);
insert into aux(line) values(1);
insert into aux(line) values(2);
insert into aux(line) values(3);
Using an auxiliary table:
select line
from aux
using a constant expression:
select 1 line
union all
select 2
union all
select 3
counting row numbers:
I found it here
SELECT
@rownum := @rownum + 1 line
FROM
(SELECT @rownum := 0) r, INFORMATION_SCHEMA.COLUMNS t
where @rownum<3
using one of the dictionary views:
SELECT
ordinal_position line
from INFORMATION_SCHEMA.COLUMNS t
where table_catalog='def'
and table_schema='information_schema'
and table_name='COLUMNS'
and ordinal_position between 1 and 3
Results:
| ORDINAL_POSITION |
|------------------|
| 1 |
| 2 |
| 3 |
Best Answer
I don't think you can combine several logs in one EXEC, so you are most likely stuck with several executions. As suggested nu Mo64, you can put it all in a temp table and then query that temp table. I have a utility that does exactly that, here.