Exporting data from one table into another table with a different structure

Question

3.00/5 (1 vote)

See more:

Hi All,
First I would like to apologise any mistake/misunderstanding as English is not my natural language.

Context: I need to pull data stored in one table(actually it is a result of joining two tables) and displaying it in a report. Please find below what I would like to accomplish in order to display the data.

Let say I have a table with the following structure and data in it:
------------------------
|code|% |nCol|nDel|day|
------------------------
101 |100| 4 | 4 |SUN|
------------------------
102 |100| 4 | 4 |SUN|
------------------------
104 |100| 6 | 6 |MON|
------------------------
101 |50 | 4 | 8 |TUE|
------------------------
102 |50 | 4 | 8 |TUE|
------------------------
104 |100| 2 | 2 |TUE|
------------------------
105 |50 | 1 | 2 |TUE|
------------------------
106 |100| 2 | 2 |TUE|
------------------------
and so on...
I would like to have as a result a table with the following structure and data in it:

|code|SUNcol|SUNdel|SUN%|MONcol|MONdel|mon%|TUEcol|TUEdel|TUE%|and so on till saturday
---------------------------------------------------------------
|101 | 4 | 4 | 100| 0 | 0 | 0 | 4 | 8 | 50 |
---------------------------------------------------------------
|102 | 4 | 4 | 100| 0 | 0 | 0 | 4 | 8 | 50 |
---------------------------------------------------------------
|103 | 0 | 0 | 0 | 6 | 6 | 100| 2 | 2 |100 |
---------------------------------------------------------------
|104 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 50 |
---------------------------------------------------------------
|105 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 2 |100 |
---------------------------------------------------------------

The result table(immediately above) should always present the entire week from Sunday till saturday.

I manage to do it through a cursor running against the first table and injecting the data into the result table, built as a temporary table. But I would like to avoid the use of cursors.

Any help with this?
Many thanks.

P.S. I'm using Sql Server 2014

What I have tried:

I have search on Codeproject and StackOverflow.

Posted 4-May-16 6:57am

jonatan_556

Updated 4-May-16 7:10am

Add a Solution

1 solution

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)

W∴ Balboos, *GHB* · Answer 1 · 2016-05-04T07:10:00

Solution 1

One method of converting column data into row data is to INNER JOIN the table to itself, multiple times

SQL

SELECT A.someVal, A.colx, B.colx, C.colx . . . . 
FROM  Table1 as A
INNER JOIN Table1 as B
ON A.someVal=B.someVal
INNER JOIN Table1 as C
on A.someVal = C.someVal
.
.
.
WHERE (etc.)

Posted 4-May-16 7:10am

W∴ Balboos, GHB

Updated 4-May-16 7:11am

v2

Comments

jonatan_556 5-May-16 5:10am

Hi ,
Thanks for the help.
I'm not sure if I can use your solution. Maybe I did not explain right myself or I did not get your point.

the table result should look as below:
|code|SUNcol|SUNdel|SUN%|MONcol|MONdel|mon%|TUEcol|TUEdel|TUE%|and so on till saturday
---------------------------------------------------------------
|101 | 4 | 4 | 100| 0 | 0 | 0 | 4 | 8 | 50 |
---------------------------------------------------------------
|102 | 4 | 4 | 100| 0 | 0 | 0 | 4 | 8 | 50 |
---------------------------------------------------------------
|103 | 0 | 0 | 0 | 6 | 6 | 100| 2 | 2 |100 |
---------------------------------------------------------------
|104 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 50 |
---------------------------------------------------------------
|105 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 2 |100 |
---------------------------------------------------------------
so basically in the table result the weekday is a column name like 'code' and the values for that code that and weekday in the source code should appear now for the code and under the weekday in the result table.

Many thanks

W∴ Balboos, GHB 5-May-16 6:52am

You the rows created by data with the same code: this is what you would INNER JOIN on.
You have the data labeled by day-of-week, so for each instance of the table (A, B, C, . . .) you qualify the WHERE clause so that they each show a single day (like A.day='SUN')

Do Not Forget To Handle Missing Entries with something like ISNULL() or you'll potentially lose rows.

Should this (or anyone else's) answers work for you, don't forget to mark the problem as solved.