Export with rotated rows

Question

This is a bit tricky to describe. I have a table with columns Name1, Name2, DoB (say). I want to output each record as follows:

Name1:  <value>
Name2:  <value>
DoB:  <value>
Name1:  <value>
Name2:  <value>
DoB.  <value>

I need to do this with various different tables at different times, and export to Excel or more likely Word.

Now I can do this OK with a report - either internal or Crystal - or by a mail merge in Word. I can also write some VBA code to do it semi-generically.

Is there any built-in way of doing it. It seems like a common export format to me. I note that the Transpose function in Word does something like this, but with columns for each different record.

Andrew

---

Andrew Gabb, Adelaide, South Australia

Answer 1

Hi Gabby22,

you had asked,"Is there any built-in way of doing it. It seems like a common export format to me"

there is no built in way to doing this.

I try to search regarding this issue.

you need to use Transpose and for that also you need the data in table format.

so you need to develop your own logic to first create a table with correct rows and columns and then try to loop through data one by one and then add it to table in word.

if the data is large then it will time consuming process.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

You could try this method. It worked for me in a test table but not sure the results or how fast it would be in a large table. This sql assumes you have a primary key field (perhaps ID as an example)

Select Name1 as DATA
FROM (SELECT Name1, ID, 1 as colnbr FROM tablename
UNION ALL SELECT Name2, ID, 2 as colnbr FROM tablename
UNION ALL SELECT DoB, ID, 3 as colnbr FROM tablename) AS [%$##@_Alias]
ORDER BY [%$##@_Alias.ID, [%$##@_Alias].colnbr;

Answer 3

Sorry about the goofy characters they might just scare you away if the whole concept of UNION ALLs doesn't beforehand:

SELECT COLS_TO_ROWS.Name1 AS DATA
FROM (SELECT Name1, ID, 1 as COLNBR FROM TABLENAME
UNION ALL SELECT Name2, ID, 2 as COLNBR FROM TABLENAME
UNION ALL SELECT Name3, ID, 3 as COLNBR FROM TABLENAME) AS COLS_TO_ROWS
ORDER BY COLS_TO_ROWS.ID, COLS_TO_ROWS.COLNBR;

This allows you to avoid VBA if that's what your after and who knows may even run much quicker depending on how much memory your computer has.

Answer 4

Thanks guys

The SQL is scary but I was looking for something more generic, ie that I could apply to any table or if wanted to adapt for special use.

I eventually wrote some VBA to do this, because I decided that I wanted to merge fields in some cases.

---

Andrew Gabb, Adelaide, South Australia