Convert multiple Rows into Columns

Question

I have a table from my script SQL e.g.:

enter image description here

I need to convert this table into:

enter image description here

I create temporary table:

CREATE TABLE #Table
(
  Data Date,
  Max Real,
  Min Real,
  Avg Real
)

I have a script, when my values are calculated and next:

INSERT INTO #Table (Data, Max, Min, Avg) VALUES (@Data, @Max, @Min, @Avg)

I don't know, how to convert table1 into table2

Maybe the links in the answer to [this question](http://stackoverflow.com/questions/20804382/sql-server-querying-multiple-rows-returning-in-one) could help you find your way. — drigoangelo, Aug 27 '14 at 12:08
@WingedPanther I know already, I need to use pivot, but I don't know how need to use pivot, I may succeed — Mini, Aug 27 '14 at 12:39

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

This is called "transposing" the data.

In some databases, the PIVOT command can be used for this purpose. When it doesn't exist, for example in MySQL, it can be done using GROUP BY and sometimes tangled SQL, as in the following examples:

It is however normally much simpler do the transposing in your import script.

A key requirement for either option is that your transposed table needs a fixed number of columns; in the example you provide, this isn't the case.

An alternative, which could be more appropriate here, is to leave the data as-is in the database, and transpose it later on in the application that uses/views the data.

edited May 23 '17 at 12:05

Community

1
1

answered Aug 27 '14 at 12:36

Hadi

203
3
9

Thank You, I'll try to do it, I may succeed – Mini Aug 27 '14 at 12:46
My data is selected from the loop and I can't enter it's "rigidly" how month in 2th link – Mini Aug 27 '14 at 13:00
@Mini , do you have a fixed number of columns, i.e. a certain number of data points, or not? Because if not, you cannot use these methods. A database table needs a predetermined number of columns. – Hadi Aug 27 '14 at 13:03
I don't use a fixed number of columns ehhh :/ – Mini Aug 27 '14 at 13:10
Then, as I mention in the answer, I suggest you store the data in the database in this format; and do the transposing when you are viewing/showing/reporting the data. – Hadi Aug 27 '14 at 13:13

score 0 · Answer 2 · answered Aug 27 '14 at 13:22

I would love to see more answers for this question here is my version

SELECT 'Data' AS [Data],* FROM (
SELECT DATA FROM #TABLE)A
PIVOT
(
MAX(DATA ) FOR DATA IN ([08:01],[08:02],[08:03],[08:04])
)B
UNION ALL
SELECT 'MAX',CONVERT(VARCHAR,[26]),CONVERT(VARCHAR,[27]),CONVERT(VARCHAR,        [28]),CONVERT(VARCHAR,[29]) FROM (
SELECT [MAX] FROM #TABLE)A
PIVOT
(
    MAX([MAX]) FOR [MAX] IN ([26],[27],[28],[29])
)B
UNION ALL
SELECT 'MIN',CONVERT(VARCHAR,[20]),CONVERT(VARCHAR,[21]),CONVERT(VARCHAR,    [22]),CONVERT(VARCHAR,[23]) FROM (
SELECT [MIN] FROM #TABLE)A
PIVOT
(
    MAX([MIN]) FOR [MIN] IN ([20],[21],[22],[23])
)B
UNION ALL
SELECT 'AVG',CONVERT(VARCHAR,[23]),CONVERT(VARCHAR,[24]),CONVERT(VARCHAR,    [25]),CONVERT(VARCHAR,[26]) FROM (
SELECT [AVG] FROM #TABLE)A
PIVOT
(
    MAX([AVG]) FOR [AVG] IN ([23],[24],[25],[26])
)B

tried to attach an image, but unable to do!

Thank You. I'll try to do it,but my table is dynamic (table isn't 4x4). whichever I will choose the time interval, so will the rows. One note: with my table 08.01, 08.02 etc. aren't time, only 1th August, 2th August etc. — Mini, Aug 27 '14 at 13:45

Convert multiple Rows into Columns

2 Answers2