Ygthkb

How do I add a strong "onion flavor" to the biryani (in restaurant style)?

Is there any danger of my neighbor having my wife's signature?

How to know if I am a 'Real Developer'

Why is Shelob considered evil?

Why do single electrical receptacles exist?

Boss asked me to sign a resignation paper without a date on it along with my new contract

Is it possible to set values for a list of variables using a for loop?

Did ancient Germans take pride in leaving the land untouched?

Is there a celebrity culture in academia and should we discourage it?

Crack the bank account's password!

Connecting to SMTP server from AWS Lambda

Is it possible to detect 100% of SQLi with a simple regex?

What do "compile" , "fit" and "predict" do in Keras sequential models?

Isn't a semicolon (';') needed after a function declaration in C++?

Is layered encryption more secure than long passwords?

Why did Ylvis use "go" instead of "say" in phrases like "Dog goes 'woof'"?

How to regain lost focus?

Is the UK legally prevented from having another referendum on Brexit?

Players preemptively rolling, even though their rolls are useless or are checking the wrong skills

Taking an academic pseudonym?

How can I give a Ranger advantage on a check due to Favored Enemy without spoiling the story for the player?

Can you say "leftside right"?

show notifications of new e-mails without displaying the content

Why write a book when there's a movie in my head?

Merge two rows into one row

SSIS Merge Join Trouble: 3 tables into 1Merge Fields into one Row with a QueryWant to combine two rows into one rowMerge a table into an existing oneTwo rows having null values merge into one row without null valuesHow to merge multiple rows into one row with SQL?Merge multiple rows into one row with sqlHelp with CTE Aggregating Children RecursivelyHow to bring data from two tables that could/could not be present in one of themData modeling with the goal to get the best performance in Oracle and SQL Server

0

So I have a table that looks like this:

And here's my query for that table:

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,d.Locationcode,c.price,

SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode                 then 1 else 0 end) as 'Selling Qty',

SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'

from masterfile.item a 

left join masterfile.itemdetail b on a.ItemCode = b.ItemCode

left join masterfile.itemprice c on a.ItemCode = c.Itemcode and 

b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass

left join masterfile.locationinv d on a.itemcode = d.itemcode and

c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class

left join masterfile.location e on d.locationcode = e.locationcode

group by b.Barcode,a.itemcode,b.color,b.size,b.class,d.locationCode,c.price

What I want is to merge these two rows, so what will happen is that selling qty and stock qty will have both 1 value because the two rows were merged.

Locationcode 001 is for selling and 002 is for stock quantity.

I want to make it look like this:

   Barcode       ItemCode      Color Size Class LocCode1 LocCode2 Price  Sell Qty   Stock Qty

   888200000001 W09118-DP28U    N/A   29    A      001      002     200      1          1

I'm kinda clueless with merging stuffs like this, so thanks in advance!

sql-server merge

share|improve this question

edited 8 mins ago

Glorfindel

9871816

asked Nov 20 '14 at 6:44

EraniichanEraniichan

184

add a comment | 

0

So I have a table that looks like this:

And here's my query for that table:

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,d.Locationcode,c.price,

SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode                 then 1 else 0 end) as 'Selling Qty',

SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'

from masterfile.item a 

left join masterfile.itemdetail b on a.ItemCode = b.ItemCode

left join masterfile.itemprice c on a.ItemCode = c.Itemcode and 

b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass

left join masterfile.locationinv d on a.itemcode = d.itemcode and

c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class

left join masterfile.location e on d.locationcode = e.locationcode

group by b.Barcode,a.itemcode,b.color,b.size,b.class,d.locationCode,c.price

What I want is to merge these two rows, so what will happen is that selling qty and stock qty will have both 1 value because the two rows were merged.

Locationcode 001 is for selling and 002 is for stock quantity.

I want to make it look like this:

   Barcode       ItemCode      Color Size Class LocCode1 LocCode2 Price  Sell Qty   Stock Qty

   888200000001 W09118-DP28U    N/A   29    A      001      002     200      1          1

I'm kinda clueless with merging stuffs like this, so thanks in advance!

sql-server merge

share|improve this question

edited 8 mins ago

Glorfindel

9871816

asked Nov 20 '14 at 6:44

EraniichanEraniichan

184

add a comment | 

So I have a table that looks like this:

And here's my query for that table:

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,d.Locationcode,c.price,

SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode                 then 1 else 0 end) as 'Selling Qty',

SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'

from masterfile.item a 

left join masterfile.itemdetail b on a.ItemCode = b.ItemCode

left join masterfile.itemprice c on a.ItemCode = c.Itemcode and 

b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass

left join masterfile.locationinv d on a.itemcode = d.itemcode and

c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class

left join masterfile.location e on d.locationcode = e.locationcode

group by b.Barcode,a.itemcode,b.color,b.size,b.class,d.locationCode,c.price

What I want is to merge these two rows, so what will happen is that selling qty and stock qty will have both 1 value because the two rows were merged.

Locationcode 001 is for selling and 002 is for stock quantity.

I want to make it look like this:

   Barcode       ItemCode      Color Size Class LocCode1 LocCode2 Price  Sell Qty   Stock Qty

   888200000001 W09118-DP28U    N/A   29    A      001      002     200      1          1

I'm kinda clueless with merging stuffs like this, so thanks in advance!

sql-server merge

share|improve this question

edited 8 mins ago

Glorfindel

9871816

asked Nov 20 '14 at 6:44

EraniichanEraniichan

184

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,d.Locationcode,c.price,

SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode                 then 1 else 0 end) as 'Selling Qty',

SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'

from masterfile.item a 

left join masterfile.itemdetail b on a.ItemCode = b.ItemCode

left join masterfile.itemprice c on a.ItemCode = c.Itemcode and 

b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass

left join masterfile.locationinv d on a.itemcode = d.itemcode and

c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class

left join masterfile.location e on d.locationcode = e.locationcode

group by b.Barcode,a.itemcode,b.color,b.size,b.class,d.locationCode,c.price

   Barcode       ItemCode      Color Size Class LocCode1 LocCode2 Price  Sell Qty   Stock Qty

   888200000001 W09118-DP28U    N/A   29    A      001      002     200      1          1

share|improve this question

edited 8 mins ago

Glorfindel

9871816

asked Nov 20 '14 at 6:44

EraniichanEraniichan

184

share|improve this question

edited 8 mins ago

Glorfindel

9871816

asked Nov 20 '14 at 6:44

EraniichanEraniichan

184

edited 8 mins ago

Glorfindel

9871816

edited 8 mins ago

Glorfindel

9871816

asked Nov 20 '14 at 6:44

EraniichanEraniichan

184

asked Nov 20 '14 at 6:44

EraniichanEraniichan

184

1 Answer
1

active

oldest

votes

0

I just removed the locationcode in select and group by clause so they will join eventually and that solved my problem.

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,c.price,

SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode         then 1 else 0 end) as 'Selling Qty',

SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'

from masterfile.item a 

left join masterfile.itemdetail b on a.ItemCode = b.ItemCode

left join masterfile.itemprice c on a.ItemCode = c.Itemcode and 

b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass

left join masterfile.locationinv d on a.itemcode = d.itemcode and

c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class

left join masterfile.location e on d.locationcode = e.locationcode

group by b.Barcode,a.itemcode,b.color,b.size,b.class,c.price

share|improve this answer

answered Nov 20 '14 at 7:23

EraniichanEraniichan

184

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f83129%2fmerge-two-rows-into-one-row%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

I just removed the locationcode in select and group by clause so they will join eventually and that solved my problem.

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,c.price,

SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode         then 1 else 0 end) as 'Selling Qty',

SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'

from masterfile.item a 

left join masterfile.itemdetail b on a.ItemCode = b.ItemCode

left join masterfile.itemprice c on a.ItemCode = c.Itemcode and 

b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass

left join masterfile.locationinv d on a.itemcode = d.itemcode and

c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class

left join masterfile.location e on d.locationcode = e.locationcode

group by b.Barcode,a.itemcode,b.color,b.size,b.class,c.price

share|improve this answer

answered Nov 20 '14 at 7:23

EraniichanEraniichan

184

add a comment | 

0

I just removed the locationcode in select and group by clause so they will join eventually and that solved my problem.

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,c.price,

SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode         then 1 else 0 end) as 'Selling Qty',

SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'

from masterfile.item a 

left join masterfile.itemdetail b on a.ItemCode = b.ItemCode

left join masterfile.itemprice c on a.ItemCode = c.Itemcode and 

b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass

left join masterfile.locationinv d on a.itemcode = d.itemcode and

c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class

left join masterfile.location e on d.locationcode = e.locationcode

group by b.Barcode,a.itemcode,b.color,b.size,b.class,c.price

share|improve this answer

answered Nov 20 '14 at 7:23

EraniichanEraniichan

184

add a comment | 

I just removed the locationcode in select and group by clause so they will join eventually and that solved my problem.

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,c.price,

SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode         then 1 else 0 end) as 'Selling Qty',

SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'

from masterfile.item a 

left join masterfile.itemdetail b on a.ItemCode = b.ItemCode

left join masterfile.itemprice c on a.ItemCode = c.Itemcode and 

b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass

left join masterfile.locationinv d on a.itemcode = d.itemcode and

c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class

left join masterfile.location e on d.locationcode = e.locationcode

group by b.Barcode,a.itemcode,b.color,b.size,b.class,c.price

share|improve this answer

answered Nov 20 '14 at 7:23

EraniichanEraniichan

184

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,c.price,

SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode         then 1 else 0 end) as 'Selling Qty',

SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'

from masterfile.item a 

left join masterfile.itemdetail b on a.ItemCode = b.ItemCode

left join masterfile.itemprice c on a.ItemCode = c.Itemcode and 

b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass

left join masterfile.locationinv d on a.itemcode = d.itemcode and

c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class

left join masterfile.location e on d.locationcode = e.locationcode

group by b.Barcode,a.itemcode,b.color,b.size,b.class,c.price

share|improve this answer

answered Nov 20 '14 at 7:23

EraniichanEraniichan

184

answered Nov 20 '14 at 7:23

EraniichanEraniichan

184

answered Nov 20 '14 at 7:23

EraniichanEraniichan

184