Styjun

Compute the square root of a positive integer using binary search

Are there any OR challenges that are similar to kaggle's competitions?

Parse a simple key=value config file in C

Does knowing that the exponent is in a certain range help solving discrete log?

Did they ever see Truman doing any private things when filming him for 24 hours 7 days a week?

Is it alright to say good afternoon Sirs and Madams in a panel interview?

Combinatorial Argument for Exponential and Logarithmic Function Being Inverse

How to use the passive form to say "This flower was watered."

What was the intention with the Commodore 128?

Unconventional examples of mathematical modelling

Why don't modern jet engines use forced exhaust mixing?

Output with the same length always

Why does this image of cyclocarbon look like a nonagon?

Gofer work in exchange for Letter of Recommendation

Ending a line of dialogue with "?!": Allowed or obnoxious?

What's a good pattern to calculate a variable only when it is used the first time?

Programming a recursive formula into Mathematica and find the nth position in the sequence

Why can't I see 1861 / 1871 census entries on Freecen website when I can see them on Ancestry website?

How do I cope with haze for the photos containing sky and trees at a distance?

Would getting a natural 20 with a penalty still count as a critical hit?

What is the best way to use errors in mathematica M12?

Did Michelle Obama have a staff of 23; and Melania have a staff of 4?

My new Acer Aspire 7 doesn't have a Legacy Boot option, what can I do to get it?

Earliest evidence of objects intended for future archaeologists?

PostgreSQL crosstab transpose row to column

SQL MAX of multiple columns?Add a column with a default value to an existing table in SQL ServerPostgreSQL “DESCRIBE TABLE”Can I concatenate multiple MySQL rows into one field?Show tables in PostgreSQLSelect first row in each GROUP BY group?What are the options for storing hierarchical data in a relational database?SQL select only rows with max value on a columnHow to exit from PostgreSQL command line utility: psqlWhich version of PostgreSQL am I running?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

Hi guys im trying to transpose a row query result to 1 row per columns

1st query is select * from sku;
the result is :

id sku sku_code travel_code 
1 sku1 1 1
2 sku1 2 2
3 sku1 3 3
4 sku1 4 4
5 sku1 5 5
6 sku1 6 6
7 sku1 7 7
8 sku2 8 8
9 sku2 9 9
10 sku2 10 10
11 sku2 11 11
12 sku2 12 12
13 sku2 13 13
14 sku2 14 14
15 sku3 15 15
16 sku3 16 16
17 sku3 17 17
18 sku3 18 18
19 sku3 19 19
20 sku3 20 21
21 sku3 21 21

expected output is

SKU SKU_CODE TRAVEL_CODE 
sku1 1 2 3 4 5 6 7 1 2 3 4 5 6 7 
sku2 8 9 10 11 12 13 14 8 9 10 11 12 13 14
sku3 15 16 17 18 19 20 21 15 16 17 18 19 20 21 

no header is ok

sql postgresql pivot

share|improve this question

edited Mar 27 at 15:23

Jake Garbo

asked Mar 27 at 13:30

Jake GarboJake Garbo

39122 silver badges1515 bronze badges

add a comment | 

0

Hi guys im trying to transpose a row query result to 1 row per columns

1st query is select * from sku;
the result is :

id sku sku_code travel_code 
1 sku1 1 1
2 sku1 2 2
3 sku1 3 3
4 sku1 4 4
5 sku1 5 5
6 sku1 6 6
7 sku1 7 7
8 sku2 8 8
9 sku2 9 9
10 sku2 10 10
11 sku2 11 11
12 sku2 12 12
13 sku2 13 13
14 sku2 14 14
15 sku3 15 15
16 sku3 16 16
17 sku3 17 17
18 sku3 18 18
19 sku3 19 19
20 sku3 20 21
21 sku3 21 21

expected output is

SKU SKU_CODE TRAVEL_CODE 
sku1 1 2 3 4 5 6 7 1 2 3 4 5 6 7 
sku2 8 9 10 11 12 13 14 8 9 10 11 12 13 14
sku3 15 16 17 18 19 20 21 15 16 17 18 19 20 21 

no header is ok

sql postgresql pivot

share|improve this question

edited Mar 27 at 15:23

Jake Garbo

asked Mar 27 at 13:30

Jake GarboJake Garbo

39122 silver badges1515 bronze badges

add a comment | 

Hi guys im trying to transpose a row query result to 1 row per columns

1st query is select * from sku;
the result is :

id sku sku_code travel_code 
1 sku1 1 1
2 sku1 2 2
3 sku1 3 3
4 sku1 4 4
5 sku1 5 5
6 sku1 6 6
7 sku1 7 7
8 sku2 8 8
9 sku2 9 9
10 sku2 10 10
11 sku2 11 11
12 sku2 12 12
13 sku2 13 13
14 sku2 14 14
15 sku3 15 15
16 sku3 16 16
17 sku3 17 17
18 sku3 18 18
19 sku3 19 19
20 sku3 20 21
21 sku3 21 21

expected output is

SKU SKU_CODE TRAVEL_CODE 
sku1 1 2 3 4 5 6 7 1 2 3 4 5 6 7 
sku2 8 9 10 11 12 13 14 8 9 10 11 12 13 14
sku3 15 16 17 18 19 20 21 15 16 17 18 19 20 21 

no header is ok

sql postgresql pivot

share|improve this question

edited Mar 27 at 15:23

Jake Garbo

asked Mar 27 at 13:30

Jake GarboJake Garbo

39122 silver badges1515 bronze badges

id sku sku_code travel_code 
1 sku1 1 1
2 sku1 2 2
3 sku1 3 3
4 sku1 4 4
5 sku1 5 5
6 sku1 6 6
7 sku1 7 7
8 sku2 8 8
9 sku2 9 9
10 sku2 10 10
11 sku2 11 11
12 sku2 12 12
13 sku2 13 13
14 sku2 14 14
15 sku3 15 15
16 sku3 16 16
17 sku3 17 17
18 sku3 18 18
19 sku3 19 19
20 sku3 20 21
21 sku3 21 21

SKU SKU_CODE TRAVEL_CODE 
sku1 1 2 3 4 5 6 7 1 2 3 4 5 6 7 
sku2 8 9 10 11 12 13 14 8 9 10 11 12 13 14
sku3 15 16 17 18 19 20 21 15 16 17 18 19 20 21 

share|improve this question

edited Mar 27 at 15:23

Jake Garbo

asked Mar 27 at 13:30

Jake GarboJake Garbo

39122 silver badges1515 bronze badges

share|improve this question

edited Mar 27 at 15:23

Jake Garbo

asked Mar 27 at 13:30

Jake GarboJake Garbo

39122 silver badges1515 bronze badges

asked Mar 27 at 13:30

Jake GarboJake Garbo

39122 silver badges1515 bronze badges

asked Mar 27 at 13:30

Jake GarboJake Garbo

39122 silver badges1515 bronze badges

2 Answers
2

active

oldest

votes

0

You can use grouping with min and max aggregation functions

with tab(sku, sku_cod, travel_code) as
(
 select 'sku1',109,'01' union all 
 select 'sku2',209,'02' union all
 select 'sku2',309,'03' union all
 select 'sku1',409,'04' 
)
select sku, min(sku_cod) as sku_cod1, max(sku_cod) as sku_cod2, 
 min(travel_code) as travel_code1, max(travel_code) as travel_code2
 from tab
 group by sku
 order by sku;

sku sku_cod1 sku_cod2 travel_code1 travel_code2
sku1 109 409 01 04
sku2 209 309 02 03

share|improve this answer

answered Mar 27 at 13:51

Barbaros ÖzhanBarbaros Özhan

20.5k77 gold badges1616 silver badges3636 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  how can i use the with tab if the select ... is came from the result of select * from ... query
  
  – Jake Garbo
  Mar 27 at 13:58
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @JakeGarbo I don't understand what you wanna say.
  
  – Barbaros Özhan
  Mar 27 at 15:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i cannot apply the 'sku1', 'sku2' statically because the query has dynamic skus
  
  – Jake Garbo
  Mar 27 at 15:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @JakeGarbo you need to share the whole requirement and sample input/output then.
  
  – Barbaros Özhan
  Mar 27 at 15:07
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  hmm i dont get your point .. but i need to make it to be 1 column per value
  
  – Jake Garbo
  Mar 27 at 15:46
  

  
  
  
  

 | 
show 2 more comments

0

You are using Postgres, so I would recommend storing the values as arrays:

select sku, array_agg(sku_cod) as sku_cods
 array_agg(travel_code) as travel_codes
from t
group by sku
order by sku;

This will allow you to handle more than two values.

share|improve this answer

answered Mar 27 at 14:34

Gordon LinoffGordon Linoff

846k3838 gold badges349349 silver badges450450 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  still didnt get the expected result ..
  
  – Jake Garbo
  Mar 27 at 14:46
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55378448%2fpostgresql-crosstab-transpose-row-to-column%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

You can use grouping with min and max aggregation functions

with tab(sku, sku_cod, travel_code) as
(
 select 'sku1',109,'01' union all 
 select 'sku2',209,'02' union all
 select 'sku2',309,'03' union all
 select 'sku1',409,'04' 
)
select sku, min(sku_cod) as sku_cod1, max(sku_cod) as sku_cod2, 
 min(travel_code) as travel_code1, max(travel_code) as travel_code2
 from tab
 group by sku
 order by sku;

sku sku_cod1 sku_cod2 travel_code1 travel_code2
sku1 109 409 01 04
sku2 209 309 02 03

share|improve this answer

answered Mar 27 at 13:51

Barbaros ÖzhanBarbaros Özhan

20.5k77 gold badges1616 silver badges3636 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  how can i use the with tab if the select ... is came from the result of select * from ... query
  
  – Jake Garbo
  Mar 27 at 13:58
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @JakeGarbo I don't understand what you wanna say.
  
  – Barbaros Özhan
  Mar 27 at 15:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i cannot apply the 'sku1', 'sku2' statically because the query has dynamic skus
  
  – Jake Garbo
  Mar 27 at 15:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @JakeGarbo you need to share the whole requirement and sample input/output then.
  
  – Barbaros Özhan
  Mar 27 at 15:07
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  hmm i dont get your point .. but i need to make it to be 1 column per value
  
  – Jake Garbo
  Mar 27 at 15:46
  

  
  
  
  

 | 
show 2 more comments

0

You can use grouping with min and max aggregation functions

with tab(sku, sku_cod, travel_code) as
(
 select 'sku1',109,'01' union all 
 select 'sku2',209,'02' union all
 select 'sku2',309,'03' union all
 select 'sku1',409,'04' 
)
select sku, min(sku_cod) as sku_cod1, max(sku_cod) as sku_cod2, 
 min(travel_code) as travel_code1, max(travel_code) as travel_code2
 from tab
 group by sku
 order by sku;

sku sku_cod1 sku_cod2 travel_code1 travel_code2
sku1 109 409 01 04
sku2 209 309 02 03

share|improve this answer

answered Mar 27 at 13:51

Barbaros ÖzhanBarbaros Özhan

20.5k77 gold badges1616 silver badges3636 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  how can i use the with tab if the select ... is came from the result of select * from ... query
  
  – Jake Garbo
  Mar 27 at 13:58
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @JakeGarbo I don't understand what you wanna say.
  
  – Barbaros Özhan
  Mar 27 at 15:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i cannot apply the 'sku1', 'sku2' statically because the query has dynamic skus
  
  – Jake Garbo
  Mar 27 at 15:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @JakeGarbo you need to share the whole requirement and sample input/output then.
  
  – Barbaros Özhan
  Mar 27 at 15:07
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  hmm i dont get your point .. but i need to make it to be 1 column per value
  
  – Jake Garbo
  Mar 27 at 15:46
  

  
  
  
  

 | 
show 2 more comments

You can use grouping with min and max aggregation functions

with tab(sku, sku_cod, travel_code) as
(
 select 'sku1',109,'01' union all 
 select 'sku2',209,'02' union all
 select 'sku2',309,'03' union all
 select 'sku1',409,'04' 
)
select sku, min(sku_cod) as sku_cod1, max(sku_cod) as sku_cod2, 
 min(travel_code) as travel_code1, max(travel_code) as travel_code2
 from tab
 group by sku
 order by sku;

sku sku_cod1 sku_cod2 travel_code1 travel_code2
sku1 109 409 01 04
sku2 209 309 02 03

share|improve this answer

answered Mar 27 at 13:51

Barbaros ÖzhanBarbaros Özhan

20.5k77 gold badges1616 silver badges3636 bronze badges

with tab(sku, sku_cod, travel_code) as
(
 select 'sku1',109,'01' union all 
 select 'sku2',209,'02' union all
 select 'sku2',309,'03' union all
 select 'sku1',409,'04' 
)
select sku, min(sku_cod) as sku_cod1, max(sku_cod) as sku_cod2, 
 min(travel_code) as travel_code1, max(travel_code) as travel_code2
 from tab
 group by sku
 order by sku;

sku sku_cod1 sku_cod2 travel_code1 travel_code2
sku1 109 409 01 04
sku2 209 309 02 03

share|improve this answer

answered Mar 27 at 13:51

Barbaros ÖzhanBarbaros Özhan

20.5k77 gold badges1616 silver badges3636 bronze badges

answered Mar 27 at 13:51

Barbaros ÖzhanBarbaros Özhan

20.5k77 gold badges1616 silver badges3636 bronze badges

answered Mar 27 at 13:51

Barbaros ÖzhanBarbaros Özhan

20.5k77 gold badges1616 silver badges3636 bronze badges

0

You are using Postgres, so I would recommend storing the values as arrays:

select sku, array_agg(sku_cod) as sku_cods
 array_agg(travel_code) as travel_codes
from t
group by sku
order by sku;

This will allow you to handle more than two values.

share|improve this answer

answered Mar 27 at 14:34

Gordon LinoffGordon Linoff

846k3838 gold badges349349 silver badges450450 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  still didnt get the expected result ..
  
  – Jake Garbo
  Mar 27 at 14:46
  

  
  
  
  

add a comment | 

0

You are using Postgres, so I would recommend storing the values as arrays:

select sku, array_agg(sku_cod) as sku_cods
 array_agg(travel_code) as travel_codes
from t
group by sku
order by sku;

This will allow you to handle more than two values.

share|improve this answer

answered Mar 27 at 14:34

Gordon LinoffGordon Linoff

846k3838 gold badges349349 silver badges450450 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  still didnt get the expected result ..
  
  – Jake Garbo
  Mar 27 at 14:46
  

  
  
  
  

add a comment | 

You are using Postgres, so I would recommend storing the values as arrays:

select sku, array_agg(sku_cod) as sku_cods
 array_agg(travel_code) as travel_codes
from t
group by sku
order by sku;

This will allow you to handle more than two values.

share|improve this answer

answered Mar 27 at 14:34

Gordon LinoffGordon Linoff

846k3838 gold badges349349 silver badges450450 bronze badges

select sku, array_agg(sku_cod) as sku_cods
 array_agg(travel_code) as travel_codes
from t
group by sku
order by sku;

share|improve this answer

answered Mar 27 at 14:34

Gordon LinoffGordon Linoff

846k3838 gold badges349349 silver badges450450 bronze badges

answered Mar 27 at 14:34

Gordon LinoffGordon Linoff

846k3838 gold badges349349 silver badges450450 bronze badges

answered Mar 27 at 14:34

Gordon LinoffGordon Linoff

846k3838 gold badges349349 silver badges450450 bronze badges