Convert Rows into Columns

Question

Post reply

Convert Rows into Columns

Viewing 15 posts - 16 through 30 (of 58 total)

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

fregatepllada SSCommitted Points: 1702 More actions · Answer 1

Thanks Jeff - I did CROSSTAB implementation before SQL 2005.

For my current project I stick with PIVOT (I have less than 10K rows). 😉

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Even if Jeff posted his great article, I wanted to post this thread where I compared the performance in 2 different scenarios.

http://www.sqlservercentral.com/Forums/Topic1537999-392-1.aspx

You could try it yourself and compare results. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

fregatepllada SSCommitted Points: 1702 More actions · Answer 3

Thanks Luis, I hate to bring another dimension to performance test but I am using TABLE variable, not temporary table. 😉

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

fregatepllada (7/17/2014)
Thanks Jeff - I did CROSSTAB implementation before SQL 2005.
For my current project I stick with PIVOT (I have less than 10K rows). 😉

Why would you intentionally use slower code just because of a small rowcount? It's like hitting wrong notes on the piano just because no one is looking. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

fregatepllada (7/17/2014)
Thanks Luis, I hate to bring another dimension to performance test but I am using TABLE variable, not temporary table. 😉

Heh... let me guess... "small row count". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

fregatepllada SSCommitted Points: 1702 More actions · Answer 6

Despite all of my protests I have to implement EAV in my current project because some genius :alien: "discovered" this anti-pattern. I do not need to do any aggregation and number of columns after PIVOT transformation could not exceed 4096 columns 🙂

zerbit SSC Veteran Points: 215 More actions · Answer 7

I dislike the solution proposed, it's far too complicated to reach a so easy task.

You requirement was to print 3 lots at a time per line of document. This mean that you should have a table with lot associated with the Line, a simple left join will suffice.

If your table looks like this

IDNoLotNoSaleOrdLine

1A1

2B1

3C1

4D2

5E2

6F2

7G2

8H2

9I2

10J2

11K3

12L3

13M3

14N3

15O3

16P3

17Q3

18R3

19S3

20T3

21U3

22V3

23W3

24X3

25Y3

26Z3

select L1.SaleOrdLine,L1.LotNo as L1,coalesce(L2.LotNo,'') as L2,coalesce(L3.LotNo,'') as L3

from [LotNo] L1

left join [LotNo] L2 on L1.saleOrdLine=L2.SaleOrdLine and L2.idno=L1.IDNo+1

left join [LotNo] L3 on L2.saleOrdLine=L3.SaleOrdLine and L3.idno=L2.IDNo+1

where L1.IDNo%3=1

with this results:

SaleOrdLineL1L2L3

1ABC

2DEF

2GHI

2J

3MNO

3PQR

3STU

3VWX

3YZ

Best regards,

PS: your solution is indeed good, but for more complex applications.

zbychbor Newbie Points: 3 More actions · Answer 8

And next solution:

regards

Zbig

;with lot(lotNo) as (

select lotNo

FROM (VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z'))t(LotNo)

)

, cte02 as (

select rn = row_number() over (order by lotNo)

,rn2 = 1 + (-1 + row_number() over (order by lotNo)) % 3

,rn3 = 1 + (-1 + row_number() over (order by lotNo)) / 3

,*

from lot

)

, cte03 as (

select rn = C1.rn

, L1 = C1.lotNo

, L2 = c2.lotNo

, L3 = c3.lotNo

from cte02 as C1

left join cte02 as C2 on C1.rn3 = C2.rn3 and c2.rn2 = 2

left join cte02 as C3 on C1.rn3 = C3.rn3 and c3.rn2 = 3

where c1.rn2 = 1

)

select L1

,L2 = isnull(L2, '')

,L3 = isnull(L3, '')

from cte03

order by rn

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

fregatepllada (7/17/2014)
Despite all of my protests I have to implement EAV in my current project because some genius :alien: "discovered" this anti-pattern. I do not need to do any aggregation and number of columns after PIVOT transformation could not exceed 4096 columns 🙂

What's that got to do with whether or not you uses a Table Variable? :blink:

Heh... and contrary to what many "experts" say, EAVs and NVPs have their place and are quite useful. EAVs are not, in and of themselves, an "anti-pattern". In fact, many types of properly-normalized tables are actually EAVs or NVPs under the hood. Think about a checkbook-like table for many different accounts like a bank would have... it has a primary key, dollar amounts, dates, an "entity" (the account number), and an attribute (deposit, automatic deposit, refunds, ATM withdrawal, check, credit card, interest, a multitude of service charges, etc, ad infinitum). 😉

If you did a pivot using either a CROSSTAB or a PIVOT, no matter if it's on numbers or text, then you used an aggregation f one form or another (MAX, for example, is an aggregation) If you used multiple self joins on the table to pivot the data instead, there's very strong likelihood that you've done it wrong. If your table is constructed to hold multiple columns of identical type data based on some criteria such as date or location, then your table doesn't follow the rules of normalization and may actually be an "anti-pattern".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

antonio.zerbinati (7/18/2014)
I dislike the solution proposed, it's far too complicated to reach a so easy task.
You requirement was to print 3 lots at a time per line of document. This mean that you should have a table with lot associated with the Line, a simple left join will suffice.

Your solution requires a table with a column of contiguous sequential numbers, which is no guarantee in real life unless you build the sequential numbers at run time.

You're also hitting the table 3 times and, while that may be appropriate in this case because the table needs to be in memory to be read even just once, there's usually more work involved (and, so, more resources used) behind the scenes to involve 3 instances of the same table using calculated self joins. While the code for a CROSS APPLY or PIVOT may be a bit longer, it can be much more effective than using calculated self joins (requires 3 full table scans, in this case).

I do, however, agree that the code in the article is quite complex for the task at hand, though. One of the single ROW_NUMBER solutions with the integer division methods for the pivot (whichever method you choose) will likely be the all-around best method for this simple task.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

zbychbor (7/18/2014)
And next solution:
regards
Zbig
;with lot(lotNo) as (
select lotNo
FROM (VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z'))t(LotNo)
)
, cte02 as (
select rn = row_number() over (order by lotNo)
,rn2 = 1 + (-1 + row_number() over (order by lotNo)) % 3
,rn3 = 1 + (-1 + row_number() over (order by lotNo)) / 3
,*
from lot
)
, cte03 as (
select rn = C1.rn
, L1 = C1.lotNo
, L2 = c2.lotNo
, L3 = c3.lotNo
from cte02 as C1
left join cte02 as C2 on C1.rn3 = C2.rn3 and c2.rn2 = 2
left join cte02 as C3 on C1.rn3 = C3.rn3 and c3.rn2 = 3
where c1.rn2 = 1
)
select L1
,L2 = isnull(L2, '')
,L3 = isnull(L3, '')
from cte03
order by rn

As I said in my previous post above, multiple self-joins is a pretty expensive way of doing this. Compound that with the multiple ROW_NUMBER and related sorts they produce and things can slow down quite a bit. Consider using one of the single ROW_NUMBER/single table pass pivoting solutions, instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

steven.bingham SSC Rookie Points: 34 More actions · Answer 12

You're usage of row_number(), modulo and such is impressive, but I

must admit that I found the solution a bit over complex and it was making

my head hurt a bit.

Consider the following code, the key is using the COALESCE statement, the

example assumes using the original 'LOTNO' table populated in the post:

DECLARE @returnResult varchar(255)

, @StartLotNumber int = 0

, @LastLotNumber int = 13

SELECT @returnResult = COALESCE(@returnResult + ',', '') + lot.LotNo, @LastLotNumber = IDNo

FROM dbo.LotNo lot

WHERE IDNo BETWEEN @StartLotNumber AND @LastLotNumber

SELECT 'Lots for Line Item#1: ' + @returnResult

select @StartLotNumber = @LastLotNumber + 1, @LastLotNumber = @StartLotNumber + 13, @returnResult = ''

SELECT @returnResult = COALESCE(@returnResult + ',', '') + lot.LotNo, @LastLotNumber = IDNo

FROM dbo.LotNo lot

WHERE IDNo BETWEEN @StartLotNumber AND @LastLotNumber

SELECT 'Lots for Line Item#2: ' + @returnResult

OUTPUT RESULTS

Lots for Line Item#1: A,B,C,D,E,F,G,H,I,J,K,L,M

Lots for Line Item#2: ,N,O,P,Q,R,S,T,U,V,W,X,Y,Z

sleon Newbie Points: 7 More actions · Answer 13

Hello Guys

Really seem like a very long way to do what pivot and unpivot do, or am I missing the point here?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 14

sleon (7/21/2014)
Hello Guys
Really seem like a very long way to do what pivot and unpivot do, or am I missing the point here?

You still need the groups and column identifiers. Read the rest of the posts to see that different methods including pivot have been posted.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

elghazal.med Newbie Points: 1 More actions · Answer 15

Hello,

I already worked on this. But i used another tool which is Stata to convert both column to rows and rows to column and i used TOS to insert Data in DB. It is great and easy.