MS Access SQL: Turn rows into column
-
I’ve tried writing queries but they just don’t work. I tried joining tables, selecting the tables more than once and renaming it using AS. I’d like to know if my desired output is really possible with mere using access sql ?? I need the query to set in my datatable for the gridview control. I am thinking of creating multiple datatable and assign them to one gridview instead of writing this complex query. But if there is a sql solution for this, I will stick to sql. Please share your ideas. Thank you. I’ve been stuck with this problem, it’s like I’m trying to turn my rows into column. Here are my tables and the desired output. Database tables: TableDATA
ID GB FREE% FS_DATE
1 0.58 43% 8/19/13 1:00 AM
2 2.42 52% 8/19/13 1:00 AM
3 4.93 2% 8/19/13 1:00 AM
4 5.12 75% 8/19/13 1:00 AM
5 0.50 1% 8/19/13 1:00 AM
6 0.582 430% 8/19/13 2:00 AM
7 2.422 520% 8/19/13 2:00 AM
8 4.932 200% 8/19/13 2:00 AM
9 5.122 750% 8/19/13 2:00 AM
10 0.522 100% 8/19/13 2:00 AMTableFS
ID FS MountPT FS_DATE SERVER_ID
1 /dev/1 a 8/19/13 1:00 AM HP-UX
2 /dev/2 b 8/19/13 1:00 AM HP-UX
3 /dev/3 c 8/19/13 1:00 AM HP-UX
4 /dev/4 d 8/19/13 1:00 AM HP-UX
5 /dev/5 e 8/19/13 1:00 AM HP-UX
6 /dev/1 a 8/19/13 2:00 AM HP-UX
7 /dev/2 b 8/19/13 2:00 AM HP-UX
8 /dev/3 c 8/19/13 2:00 AM HP-UX
9 /dev/4 d 8/19/13 2:00 AM HP-UX
10 /dev/5 e 8/19/13 2:00 AM HP-UXRequired Output:
(8/19/13 1:00) (8/19/13 2:00) (8/19/13 3:00 and so on.....)FS MountPT GB FREE% GB FREE%
/dev/1 a 0.58 43% 0.582 430%
/dev/2 b 2.42 52% 2.422 520%
/dev/3 c 4.93 2% 4.932 200%
/dev/4 d 5.12 75% 5.122 750%
/dev/5 e 0.50 1% 0.522 100% -
I’ve tried writing queries but they just don’t work. I tried joining tables, selecting the tables more than once and renaming it using AS. I’d like to know if my desired output is really possible with mere using access sql ?? I need the query to set in my datatable for the gridview control. I am thinking of creating multiple datatable and assign them to one gridview instead of writing this complex query. But if there is a sql solution for this, I will stick to sql. Please share your ideas. Thank you. I’ve been stuck with this problem, it’s like I’m trying to turn my rows into column. Here are my tables and the desired output. Database tables: TableDATA
ID GB FREE% FS_DATE
1 0.58 43% 8/19/13 1:00 AM
2 2.42 52% 8/19/13 1:00 AM
3 4.93 2% 8/19/13 1:00 AM
4 5.12 75% 8/19/13 1:00 AM
5 0.50 1% 8/19/13 1:00 AM
6 0.582 430% 8/19/13 2:00 AM
7 2.422 520% 8/19/13 2:00 AM
8 4.932 200% 8/19/13 2:00 AM
9 5.122 750% 8/19/13 2:00 AM
10 0.522 100% 8/19/13 2:00 AMTableFS
ID FS MountPT FS_DATE SERVER_ID
1 /dev/1 a 8/19/13 1:00 AM HP-UX
2 /dev/2 b 8/19/13 1:00 AM HP-UX
3 /dev/3 c 8/19/13 1:00 AM HP-UX
4 /dev/4 d 8/19/13 1:00 AM HP-UX
5 /dev/5 e 8/19/13 1:00 AM HP-UX
6 /dev/1 a 8/19/13 2:00 AM HP-UX
7 /dev/2 b 8/19/13 2:00 AM HP-UX
8 /dev/3 c 8/19/13 2:00 AM HP-UX
9 /dev/4 d 8/19/13 2:00 AM HP-UX
10 /dev/5 e 8/19/13 2:00 AM HP-UXRequired Output:
(8/19/13 1:00) (8/19/13 2:00) (8/19/13 3:00 and so on.....)FS MountPT GB FREE% GB FREE%
/dev/1 a 0.58 43% 0.582 430%
/dev/2 b 2.42 52% 2.422 520%
/dev/3 c 4.93 2% 4.932 200%
/dev/4 d 5.12 75% 5.122 750%
/dev/5 e 0.50 1% 0.522 100%This article [^]may help, it is designed to work with SQL Server, I don't know if it will work with Access. It requires 2 nested queries, fist prepare your data so you have exactly what is required (join the table) and get the column names to pivot on (Date/Times). This pivot the data on the date/time column.
Never underestimate the power of human stupidity RAH