sql query - needs transposing a column

[03021979]

Hi, here is a part of database:

SO CODE_ID EXT_TEXT

1251111 1 aaaa
1251111 2 bbbb
1251113 1 cccc
1251113 2 dddd

I need to write excel sql query to get something like this

SO EXT_TEXT_LINE_1 EXT_TEXT_LINE_2

1251111 aaaa bbbb
1251113 cccc dddd


The problem is how to transpose EXT_TEXT into two separate columns as a result of this query.

Anyone could help?

 

[gmmastros]

How many CODE_Id's can exist for each SO ? Is it always 2, or could there be more?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

I need to write excel sql query to get something like this

What does this mean (excel sql query )?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[03021979]

SQL Denis: sorry for this "excel sql query" I meant that this query returns data to excel

gmmastros: Let's assume that CODE_Id may take only two values: 1 or 2.

 

[SQLDenis]

here is one way

create table #test (field1 int, field2 tinyint ,field3 varchar(50))
insert into #test select 1251111,  1,          'aaaa' union all
select 1251111 , 2 ,         'bbbb' union all
select 1251113 , 1 ,         'cccc' union all
select 1251113 , 2 ,         'dddd' 

select * from #test

select t1.field1 , t1.field3,t2.field3
from #test t1 join #test t2 on t1.field1 =t2.field1
and t1.field2 < t2.field2

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

select so,
	min(case when code_id = 1 then ext_text end) as ext_text_line_1,
	min(case when code_id = 2 then ext_text end) as ext_text_line_2
from blah
group by so
order by so

Btw. if for some reason code_i has more than two values... .

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[03021979]

For some reason code_id can have 25 values

 

[SQLDenis]

??? gmmastros: Let's assume that CODE_Id may take only two values: 1 or 2.

?? For some reason code_id can have 25 values

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[03021979]

SQl Denis: I wrote that we could assume we have only two value to be easier. CODE_ID my have values from 1 to 25 (each value is a separate text comment line). But only the first two lines are concerned.

 

[vongrunt]

> For some reason code_id can have 25 values

No prob, repeat MIN() line for each CODE_ID value possible - up to ext_text_line_25.

Of course - when someone adds 26th value in table you'll have to modify query

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[03021979]

vongrunt:

what is "min" in your query for?

 

[vongrunt]

GROUP BY requires it, otherwise it has no any meaningful purpose for transposition - except maybe if two rows have same SO and CODE_ID, in which case it will take smaller EXT_TEXT value (alphabetically).

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[03021979]

there is no possibility to have the same SO and CODE_ID so there is no problem!

 

[vongrunt]

MIN() or similar aggregate actually does 50% of transposition. Run this:

select so,
    case when code_id = 1 then ext_text end as ext_text_line_1,
    case when code_id = 2 then ext_text end as ext_text_line_2
from blah
order by so

You'll see values are nicely placed diagonally. MIN() does the rest.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[03021979]

many thanks! I will see it tomorrow as I have to check it in my company!

 

[19790203]

I tried to put your idea into:

StrSqlInvExt = "SELECT INVCEHED.CUSN65, INVCEHED.DTIN65, OEP845L2.TENT45, DELADDS.ONAM45, DELADDS.OAD445, TRIM(DELADDS.OAD545), OEP040L1.SVIA40, INVCEHED.INVN65, INVCEHED.CUSO65, SQP608L1.ICAT08, OEP040L1.CORD40, INVCEHED.ORDN65, min(case when INP40.TLNO40='1' then INP40.TLIN40 end) as ext_text_line_1, INVBYORD.LQTY70, INVBYORD.DGRP70, INVBYORD.DGRP70, PARTS.PGMN35, PARTS.PDES35, " _
& "INU056L1.XPIF56, INU056L1.XDIF56, INU056L1.WNET56, INP056L1.IDMD56, OEP845L2.IPNR45, INVCEHED.CURN65, INVCEHED.CURC65, INVCEHED.CURT65, min(case when INP40.TLNO40='2' then INP40.TLIN40 end) as ext_text_line_2 " _
& Chr(13) & "" & Chr(10) & _
"FROM EUROPEAN.TVSPF00.DELADDS DELADDS, EUROPEAN.TVSPF00.INP056L1 INP056L1, EUROPEAN.TVSPF00.INP40 INP40, EUROPEAN.TVSPF00.INU056L1 INU056L1, EUROPEAN.TVSPF00.INVBYORD INVBYORD, EUROPEAN.TVSPF00.INVCEHED INVCEHED, " _
& " EUROPEAN.TVSPF00.OEP040L1 OEP040L1, EUROPEAN.TVSPF00.OEP065 OEP065, EUROPEAN.TVSPF00.OEP845L2 OEP845L2, EUROPEAN.TVSPF00.PARTS PARTS, EUROPEAN.TVSPF00.SQP608L1 SQP608L1 " _
& Chr(13) & "" & Chr(10) & _
"WHERE INVCEHED.CONO65 = OEP065.CONO65 AND INVCEHED.INVN65 = OEP065.INVN65 AND INVCEHED.CONO65 = INVBYORD.CONO70 AND INVCEHED.INVN65 = INVBYORD.INVN70 AND INVBYORD.CONO70 = PARTS.CONO35 AND INVBYORD.CATN70 = PARTS.PNUM35 AND INVCEHED.CONO65 = DELADDS.CONO45 AND INVCEHED.ORDN65 = DELADDS.ORDN45 AND INVCEHED.CONO65 = INP056L1.CONO56 AND INVCEHED.ORDN65 = INP056L1.ORDN56 AND INVCEHED.DESN65 = INP056L1.DESN56 AND INVCEHED.CONO65 = INU056L1.CONO56 AND INVCEHED.ORDN65 = INU056L1.ORDN56 AND " _
& " INVCEHED.DESN65 = INU056L1.DESN56 AND INVCEHED.CONO65 = OEP845L2.CONO45 AND INVCEHED.ORDN65 = OEP845L2.ISHO45 AND (INP40.TTYP40='O') AND (INP40.USGC40='E') and INVCEHED.DESN65 = OEP845L2.IDNS45 AND INVCEHED.CONO65 = OEP040L1.CONO40 AND INVCEHED.ORDN65 = OEP040L1.ORDN40 AND OEP040L1.CONO40 = INP40.CONO40 AND OEP040L1.ORDN40 = INP40.TREF40 AND INVBYORD.CONO70 = SQP608L1.CONO08 AND INVBYORD.CATN70 = SQP608L1.PNUM08 AND " _
& " ((INVCEHED.CONO65='88') AND " _
& " (INVCEHED.INVN65 Between '" & jba_from & "' and '" & jba_to & "')) group by INVCEHED.INVN65 ORDER BY INVCEHED.INVN65 DESC" _


but there appears sql query error. what is wrong?

 

[19790203]

I'd like to say aditionally that it works without this case statements....