Query to write extra rows in Excel output

Question

I'm trying to accomplish something that seems like it should be straightforward in MS Excel. I want to use a single SQL query - so I can pass it on to others to copy and paste - though I know the following could be achieved with other methods as well. Sheet 1 looks like this:

ID        value          value_type
1         minneapolis    city_name
2         cincinnati     city_name

I want an SQL query to return an "exploded" version of those two rows:

ID        attr_name      attr_value
1         value          minneapolis
1         value_type     city_name
2         value          cincinnati
2         value_type     city_name

There's much more I need to do, but this concept gets at the heart of the issue. I've tried a single SELECT statement, but can't seem to make it create two rows from one, and when I tried using UNION ALL I got a syntax error.

In Microsoft Query, how can I construct an SQL statement to create two rows from the existing values in one row?

UPDATE

thanks for the help so far. First, for reference, here is the default statement that recreates the table in Microsoft Query:

SELECT
    `Sheet3$`.ID,
    `Sheet3$`.name,
    `Sheet3$`.name_type
FROM `path\testconvert.xlsx`.`Sheet3$` `Sheet3$`

So, following @lad2025's lead, I have:

SELECT
   ID = `Sheet3$`.ID
   ,attr_name  = 'value'
   ,attr_value = `Sheet3$`.value
FROM `path\testconvert.xlsx`.`Sheet3$` `Sheet3$`
UNION ALL
SELECT 
    ID = `Sheet3$`.ID
    ,attr_name  = 'value_type'
    ,attr_value = `Sheet3$`.value_type
FROM `path\testconvert.xlsx`.`Sheet3$` `Sheet3$`

And the result is this error Too few parameters. Expected 4.

Soooo kind of off-topic, but why are you doing this? This smells like an XY problem. — Kyle Hale, Oct 08 '15 at 16:47
fair question, I'm trying to simplify the conversion process from the flat table format in the example to that second format, which ultimate becomes a .csv upload file to a database system. XY will be involved, but only to turn lat/long fields into WKT (if that's the XY your are talking about...) — mr.adam, Oct 08 '15 at 17:08
http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem — Kyle Hale, Oct 08 '15 at 21:25
I see. You can certainly down-vote it if you like, I don't care. I was asking about a specific operation in a specific environment. Looked around for a while for some guidance, but could find any that helped. As you can see below, the main trickiness was not with the SQL, but with the syntax. That's why I was specific about the environment. — mr.adam, Oct 08 '15 at 21:55
: ) I never downvote, and this is a perfectly valid question. But as a guy who's worked on databases for 15 years, I would never want to work in a system that took the input you're generating with that SQL statement. — Kyle Hale, Oct 09 '15 at 13:51
yeah, gotchya, hence the desire to make an easy conversion process. luckily it's an initial legacy data upload, so it's hopefully a one time ordeal... — mr.adam, Oct 09 '15 at 14:46

score 2 · Answer 1 · answered Oct 08 '15 at 16:41

2

LiveDemo

CREATE TABLE #mytable(
   ID         INTEGER  NOT NULL PRIMARY KEY 
  ,value      VARCHAR(11) NOT NULL
  ,value_type VARCHAR(9) NOT NULL
);
INSERT INTO #mytable(ID,value,value_type) VALUES (1,'minneapolis','city_name');
INSERT INTO #mytable(ID,value,value_type) VALUES (2,'cincinnati','city_name');


SELECT 
    ID
   ,[attr_name]  = 'value'
   ,[attr_value] = value
FROM #mytable
UNION ALL
SELECT 
    ID
   ,[attr_name]  = 'value_type'
   ,[attr_value] = value_type
FROM #mytable
ORDER BY id;

answered Oct 08 '15 at 16:41

Lukasz Szozda

162,964
23
234
275

Ok, this looks good, but Microsoft Query seems to have some funny syntax/bracket/``` rules that I'll have to mess around with... – mr.adam Oct 08 '15 at 17:08
@mr.adam Try without brackets, simply `attr_name` and `attr_value` – Lukasz Szozda Oct 08 '15 at 17:14
added update. removing the brackets was necessary because there was a special error message for them, but still haven't solved the overall problem. – mr.adam Oct 08 '15 at 17:36
@mr.adam try your updated query with `,attr_name = "value"` Change`'` to `"` – Lukasz Szozda Oct 08 '15 at 17:43
now it says 6 parameters are required :(. I tried using this format `\`Sheet3$\`.ID AS ID` or `AS 'ID'`, but still no luck. Always some missing parameters. – mr.adam Oct 08 '15 at 17:49
@mr.adam How about `Sheet3$`.ID AS [ID] – Lukasz Szozda Oct 08 '15 at 17:54
now 3 parameters are expected. looking that error up, it seems that it's often from unbracketed field names with special characters. I've removed the underscores and things still aren't working. let me know if you think moving this to chat would be useful... – mr.adam Oct 08 '15 at 18:03

score 0 · Answer 2 · answered Oct 08 '15 at 19:34

Ok, after going back to the original statement and working up from there as per the suggestions from @lad2025, I've come up with this statement which achieves what I was looking for in my original question:

SELECT 
    ID,
    'name' AS [attr_name],
    name AS [attr_value] 
FROM `path\testconvert.xlsx`.`Sheet3$` `Sheet3$`
UNION ALL
SELECT 
    ID,
    'name_type',
    name_type
FROM `path\testconvert.xlsx`.`Sheet3$` `Sheet3$`
ORDER BY ID;

One of the main problems is that the new column names are only defined in the first SELECT statement. Also, brackets are ok, just not how @lad2025 was using them originally.

Microsoft Query is pretty finicky.

Query to write extra rows in Excel output

2 Answers2