Microsoft Access Transform Table

Question

1.80/5 (3 votes)

See more:

dear all....

Help me plzz...

I have Table:

----------------------------
Name    C1    C2    C3    C4
----------------------------
t1001   10    20    30    40
t1002   1      2     3    4
t1003   11    12    13    14
t1004   5     10    15    20
----------------------------

i wan to get output like this :

-----------------------------------------
Name    t1001    t1002    t1003    t1004
-----------------------------------------
C1      10       1        11       5
C2      20       2        12       10
C3      30       3        13       15
C4      40       4        14       20
-----------------------------------------

I really hope some one can help me out here!
Thanks in advance :)

Posted 12-Jul-15 5:12am

mabaega

Updated 12-Jul-15 6:15am

Wendelius

v2

Add a Solution

Comments

RedDk 12-Jul-15 13:34pm

TSQL is one way to go (ttuMW) ... but Access has it's own "transform" it's just called a "crosstab" query. Reach out and depress the "Query Wizard" button and the dropdown menu will likely allow you to create one through the "Crosstab Query" flavor.

And don't let the bonehead who downvoted your question get YOU down. This stuff happens here in QA all the time. You're question's more than legitimate.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-07-12T06:26:00

Rotating the result set is normally done using PIVOT or TRANSFORM operation. Access has a TRANSFORM statement which could be used in this case. See TRANSFORM Statement (Microsoft Access SQL)[^]

However, if this has to do with something reporting or similar, I would advise to do all the formatting in reporting tool, not in the SQL. This will save a lot of headache.

Having that said, if the amount of rows is static, you can also consider something like

If the original query is

SQL

SELECT Name, C1, C2, C3, C4
FROM MyTable
WHERE Name IN ('t1001','t1002','t1003','t1004')

you could 'rotate' it like this:

SQL

SELECT 'C1' AS Name,
       t1.c1 as 't1001',
       t2.c1 as 't1002',
       t3.c1 as 't1003',
       t4.c1 as 't1004'
FROM MyTable t1,
     MyTable t2,
     MyTable t3,
     MyTable t4,
WHERE t1.Name = 't1001'
AND   t2.Name = 't1002'
AND   t3.Name = 't1003'
AND   t4.Name = 't1004'
UNION ALL
SELECT 'C2' AS Name,
       t1.c2 as 't1001',
       t2.c2 as 't1002',
       t3.c2 as 't1003',
       t4.c2 as 't1004'
FROM MyTable t1,
     MyTable t2,
     MyTable t3,
     MyTable t4,
WHERE t1.Name = 't1001'
AND   t2.Name = 't1002'
AND   t3.Name = 't1003'
AND   t4.Name = 't1004'
UNION ALL
SELECT 'C3' AS Name,
       t1.c3 as 't1001',
       t2.c3 as 't1002',
       t3.c3 as 't1003',
       t4.c3 as 't1004'
FROM MyTable t1,
     MyTable t2,
     MyTable t3,
     MyTable t4,
WHERE t1.Name = 't1001'
AND   t2.Name = 't1002'
AND   t3.Name = 't1003'
AND   t4.Name = 't1004' and so on

mabaega · Accepted Answer · 2015-07-12T23:01:00

Thanks for a given solution [Solution 1].

This solution does not answer my problem , but give me idea to solve my problem .

Throughout I need is [ColName] and [Value] from a specific column [Name] , I create a new query for each column (Cx) and combine them using the "UNION ALL"

Create new Query >> MyQuery

SQL

SELECT [Name], "C1" AS ColName, [C1] as [Value] FROM MyTable
UNION ALL
SELECt [Name], "C2" AS ColName, [C2] as [Value] FROM MyTable
UNION ALL
SELECt [Name], "C3" AS ColName, [C3] as [Value] FROM MyTable
UNION ALL 
SELECt [Name], "C4" AS ColName, [C4] as [Value] FROM MyTable;

Query Output :

SQL

-----------------------
Name	ColName	Value
-----------------------
t1001   C1		10
t1002	C1		1
t1003	C1		11
t1004	C1		5
t1001   C2		20
t1002	C2		2
t1003	C2		12
t1004	C2		10
t1001   C3		30
t1002	C3		3
t1003	C3		13
t1004	C3		15
...
-----------------------

then i can get what i need through the application that i created.

reQuery from Application :

SQL

sSQL = "SELECT MyQuery.[ColName], SUM(MyQuery.[Value]) AS SumValue " & _
       " FROM MyQuery WHERE(MyQuery.[Name] = '" & Param1 & "') " & _
       " GROUP BY Left(MyQuery.[Name], MyQuery.[ColName];"

Output : Param1 = t1001

SQL

---------------
ColName	Value
---------------
C1	10
C2	20
C3	30
C4	40
---------------

thank's & sorry for my english... :)

Microsoft Access Transform Table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us