Sql script Transpose Multiple Column into Rows

Question

Sql script Transpose Multiple Column into Rows

Bomby

SSC Enthusiast

Points: 173
More actions
September 30, 2019 at 12:42 pm

#3684524

Hi

i've the current situation
DtCont | DtCont | DtCont |
---------------------------------------------------------
"20180229"|"20180330"|"20180428"
and i need the follow result
|DtCont |
------------------------------------------
|"20180229"|
|"20180330"|
|"20180428"|

With Unpivot is not possible,only solutuon i find is create a sequente of select with union
select a.PM_DataContabileBKI as r0
from (
SELECT top(1) PM_DataContabileBKI
FROM [xxx].[xxx].[xxx]
order by 1 desc
)as a
union
select DATEADD( MONTH,-1,a.PM_DataContabileBKI) r0
from (
SELECT top(1) PM_DataContabileBKI
FROM [xxx].[xxx].[xxx]
order by 1 desc
)as a

But is not performing with 36 union.......
Any Idea?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24387 More actions · Answer 1

This looks like you are simply repeating the same query over and over

SELECT top(1) PM_DataContabileBKI
FROM [xxx].[xxx].[xxx]
order by 1 desc

and subtracting a number of months from the single value returned.

Why not get the value once, and cross apply a list of months to offset

How to post data/code on a forum to get the best help.

Bomby SSC Enthusiast Points: 173 More actions · Answer 2

If i wrote

SELECT PM_DataContabileBKI,DATEADD( MONTH,-7,PM_DataContabileBKI) r7

FROM [xxx].[xxx].[xxx]

order by 1 desc

the result is a list of column but i nedd a list of row 🙂

DesNorton SSC-Insane Points: 24387 More actions · Answer 3

That SQL does not make sense.

Kindly provide DDL scripts and sample data so that we can understand what you are seeing.

How to post data/code on a forum to get the best help.

Lowell SSC Guru Points: 323518 More actions · Answer 4

a simple basic example of cross applying a datamath function to get your 36 values based on one date.your example did not go by date, it seemed, though; are you trying to get the last two days of the month?

SELECT name,ExpandedMonth
FROM sys.tables t
CROSS APPLY 
  (
    SELECT TOP 36
    DATEADD(mm, -ROW_NUMBER() OVER (ORDER BY sc1.id) ,t.[create_date]) AS ExpandedMonth
    FROM Master.dbo.SysColumns sc1
  ) X
ORDER BY name,ExpandedMonth

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Joseph.caldwell 19590 SSC-Addicted Points: 465 More actions · Answer 5

UnPivot works for this

Create Table #Test (Id Int, DtCont1 char(8), DtCont2 char(8), DtCont3 char(8))

Insert #Test (Id, DtCont1, DtCont2, DtCont3) Values (1, '20180229', '20180330', '20180428')


SELECT Id, DtCont
FROM #Test
UNPIVOT
(
DtCont
FOR Course in (DtCont1, DtCont2, DtCont3)
) AS TestUnpivot

Output:

Id DtCont

1 20180229

1 20180330

1 20180428

With more data:

Insert #Test (Id, DtCont1, DtCont2, DtCont3) Values (1, '20180229', '20180330', '20180428')
Insert #Test (Id, DtCont1, DtCont2, DtCont3) Values (2, '20180220', '20180331', '20180429')
Insert #Test (Id, DtCont1, DtCont2, DtCont3) Values (3, '20180222', '20180332', '20180420')

Id DtCont

1 20180229

1 20180330

1 20180428

2 20180220

2 20180331

2 20180429

3 20180222

3 20180332

3 20180420