Please help SQL in Visual Studio c#
up vote
0
down vote
favorite
1
I had 2 tables which tblProduct and tblStock
tblProduct
ProdName Price
P8 234
S7 234
M8 234
MD8oz 234
TO8 306
TO8JP 354
LR8 354
GBB8 354
GTF8 354
SS8 354
MD12 340
P750 322
S750 322
MD750 322
P300 132
S300 132
M300 132
MD NEON300 132
1.25L 228
CANS 250ML 348
CANS 330ML 552
TO355 585
LR330X12 210
G350 576
G500 760
SSE300X24 444
SSE300X12 222
PW350 254
PW500 230
PW1L 220
tblstock
ProdName Quantity INdate PriceperQty TranStatus
M300 2 2018-11-07 23:46:05.800 132 IN
M300 2 2018-11-08 11:23:34.227 132 OUT
P300 23 2018-11-08 16:52:01.260 132 IN
MD12 12 2018-11-08 16:52:10.147 340 IN
TO8JP 3 2018-11-08 16:52:18.843 354 IN
SSE300X24 13 2018-11-08 16:52:28.587 444 IN
P8 43 2018-11-08 16:52:37.237 234 IN
1.25L 21 2018-11-08 16:52:43.843 228 IN
P8 32 2018-11-08 16:52:50.050 234 IN
S7 43 2018-11-08 16:52:54.900 234 IN
S7 32 2018-11-08 16:53:07.460 234 IN
P8 23 2018-11-08 16:53:19.020 234 IN
I want to produce the results like below from these two tables.
sql
sql-server tsql edited Nov 8 at 23:16
halfer
14.1k757104
asked Nov 8 at 9:34
mr.jm
186
|
show 4 more comments
up vote
0
down vote
favorite
1
I had 2 tables which tblProduct and tblStock
tblProduct
ProdName Price
P8 234
S7 234
M8 234
MD8oz 234
TO8 306
TO8JP 354
LR8 354
GBB8 354
GTF8 354
SS8 354
MD12 340
P750 322
S750 322
MD750 322
P300 132
S300 132
M300 132
MD NEON300 132
1.25L 228
CANS 250ML 348
CANS 330ML 552
TO355 585
LR330X12 210
G350 576
G500 760
SSE300X24 444
SSE300X12 222
PW350 254
PW500 230
PW1L 220
tblstock
ProdName Quantity INdate PriceperQty TranStatus
M300 2 2018-11-07 23:46:05.800 132 IN
M300 2 2018-11-08 11:23:34.227 132 OUT
P300 23 2018-11-08 16:52:01.260 132 IN
MD12 12 2018-11-08 16:52:10.147 340 IN
TO8JP 3 2018-11-08 16:52:18.843 354 IN
SSE300X24 13 2018-11-08 16:52:28.587 444 IN
P8 43 2018-11-08 16:52:37.237 234 IN
1.25L 21 2018-11-08 16:52:43.843 228 IN
P8 32 2018-11-08 16:52:50.050 234 IN
S7 43 2018-11-08 16:52:54.900 234 IN
S7 32 2018-11-08 16:53:07.460 234 IN
P8 23 2018-11-08 16:53:19.020 234 IN
I want to produce the results like below from these two tables.
sql
sql-server tsql edited Nov 8 at 23:16
halfer
14.1k757104
asked Nov 8 at 9:34
mr.jm
186
1
What have to tried so far, can you post your attempt SQL please? What were the errors you got, or what was wrong with the results? Stack Overflow isn't a free coding service, it's for asking question for specific coding problems. You need to show us your code first for us to be able to help you fix it.
– Larnu
Nov 8 at 9:36
@Larnu im using this query select min(a.ProdName), max(b.Quantity),max(b.Size),min(b.Reference) from tblProd a INNER JOIN tblStock b ON a.ID = b.ProdID
– mr.jm
Nov 8 at 9:38
@Larnu also thi query but I can't show the load 1,2,3 select min(a.ProdName), max(b.Quantity) from tblProd a INNER JOIN tblStock b ON a.ID = b.ProdID where b.TranStatus = 'IN' and b.Reference = '0' group by a.ProdName,b.Credat
– mr.jm
Nov 8 at 9:41
I'd guess you could use ROW NUMBER OVER PARTITION to put tblstock into order and get a load number for each row, then PIVOT to turn that into the table you want. But I'm not an expert here. Or you've put C# in the title: are you asking how to do this in LINQ not SQL?
– Rup
Nov 8 at 9:41
1
Put the (formatted) SQL in your question please, not in the comments, @mr.jm
– Larnu
Nov 8 at 9:42
|
show 4 more comments
up vote
0
down vote
favorite
1
up vote
0
down vote
favorite
1
1
I had 2 tables which tblProduct and tblStock
tblProduct
ProdName Price
P8 234
S7 234
M8 234
MD8oz 234
TO8 306
TO8JP 354
LR8 354
GBB8 354
GTF8 354
SS8 354
MD12 340
P750 322
S750 322
MD750 322
P300 132
S300 132
M300 132
MD NEON300 132
1.25L 228
CANS 250ML 348
CANS 330ML 552
TO355 585
LR330X12 210
G350 576
G500 760
SSE300X24 444
SSE300X12 222
PW350 254
PW500 230
PW1L 220
tblstock
ProdName Quantity INdate PriceperQty TranStatus
M300 2 2018-11-07 23:46:05.800 132 IN
M300 2 2018-11-08 11:23:34.227 132 OUT
P300 23 2018-11-08 16:52:01.260 132 IN
MD12 12 2018-11-08 16:52:10.147 340 IN
TO8JP 3 2018-11-08 16:52:18.843 354 IN
SSE300X24 13 2018-11-08 16:52:28.587 444 IN
P8 43 2018-11-08 16:52:37.237 234 IN
1.25L 21 2018-11-08 16:52:43.843 228 IN
P8 32 2018-11-08 16:52:50.050 234 IN
S7 43 2018-11-08 16:52:54.900 234 IN
S7 32 2018-11-08 16:53:07.460 234 IN
P8 23 2018-11-08 16:53:19.020 234 IN
I want to produce the results like below from these two tables.
sql
sql-server tsql edited Nov 8 at 23:16
halfer
14.1k757104
asked Nov 8 at 9:34
mr.jm
186
I had 2 tables which tblProduct and tblStock
tblProduct
ProdName Price
P8 234
S7 234
M8 234
MD8oz 234
TO8 306
TO8JP 354
LR8 354
GBB8 354
GTF8 354
SS8 354
MD12 340
P750 322
S750 322
MD750 322
P300 132
S300 132
M300 132
MD NEON300 132
1.25L 228
CANS 250ML 348
CANS 330ML 552
TO355 585
LR330X12 210
G350 576
G500 760
SSE300X24 444
SSE300X12 222
PW350 254
PW500 230
PW1L 220
tblstock
ProdName Quantity INdate PriceperQty TranStatus
M300 2 2018-11-07 23:46:05.800 132 IN
M300 2 2018-11-08 11:23:34.227 132 OUT
P300 23 2018-11-08 16:52:01.260 132 IN
MD12 12 2018-11-08 16:52:10.147 340 IN
TO8JP 3 2018-11-08 16:52:18.843 354 IN
SSE300X24 13 2018-11-08 16:52:28.587 444 IN
P8 43 2018-11-08 16:52:37.237 234 IN
1.25L 21 2018-11-08 16:52:43.843 228 IN
P8 32 2018-11-08 16:52:50.050 234 IN
S7 43 2018-11-08 16:52:54.900 234 IN
S7 32 2018-11-08 16:53:07.460 234 IN
P8 23 2018-11-08 16:53:19.020 234 IN
I want to produce the results like below from these two tables.
sql
sql-server tsql sql
sql-server tsql edited Nov 8 at 23:16
halfer
14.1k757104
asked Nov 8 at 9:34
mr.jm
186
edited Nov 8 at 23:16
halfer
14.1k757104
asked Nov 8 at 9:34
mr.jm
186
edited Nov 8 at 23:16
halfer
14.1k757104
edited Nov 8 at 23:16
halfer
14.1k757104
edited Nov 8 at 23:16
halfer
14.1k757104
14.1k757104
asked Nov 8 at 9:34
mr.jm
186
asked Nov 8 at 9:34
mr.jm
186
asked Nov 8 at 9:34
mr.jm
186
186
1
What have to tried so far, can you post your attempt SQL please? What were the errors you got, or what was wrong with the results? Stack Overflow isn't a free coding service, it's for asking question for specific coding problems. You need to show us your code first for us to be able to help you fix it.
– Larnu
Nov 8 at 9:36
@Larnu im using this query select min(a.ProdName), max(b.Quantity),max(b.Size),min(b.Reference) from tblProd a INNER JOIN tblStock b ON a.ID = b.ProdID
– mr.jm
Nov 8 at 9:38
@Larnu also thi query but I can't show the load 1,2,3 select min(a.ProdName), max(b.Quantity) from tblProd a INNER JOIN tblStock b ON a.ID = b.ProdID where b.TranStatus = 'IN' and b.Reference = '0' group by a.ProdName,b.Credat
– mr.jm
Nov 8 at 9:41
I'd guess you could use ROW NUMBER OVER PARTITION to put tblstock into order and get a load number for each row, then PIVOT to turn that into the table you want. But I'm not an expert here. Or you've put C# in the title: are you asking how to do this in LINQ not SQL?
– Rup
Nov 8 at 9:41
1
Put the (formatted) SQL in your question please, not in the comments, @mr.jm
– Larnu
Nov 8 at 9:42
|
show 4 more comments
1
What have to tried so far, can you post your attempt SQL please? What were the errors you got, or what was wrong with the results? Stack Overflow isn't a free coding service, it's for asking question for specific coding problems. You need to show us your code first for us to be able to help you fix it.
– Larnu
Nov 8 at 9:36
@Larnu im using this query select min(a.ProdName), max(b.Quantity),max(b.Size),min(b.Reference) from tblProd a INNER JOIN tblStock b ON a.ID = b.ProdID
– mr.jm
Nov 8 at 9:38
@Larnu also thi query but I can't show the load 1,2,3 select min(a.ProdName), max(b.Quantity) from tblProd a INNER JOIN tblStock b ON a.ID = b.ProdID where b.TranStatus = 'IN' and b.Reference = '0' group by a.ProdName,b.Credat
– mr.jm
Nov 8 at 9:41
I'd guess you could use ROW NUMBER OVER PARTITION to put tblstock into order and get a load number for each row, then PIVOT to turn that into the table you want. But I'm not an expert here. Or you've put C# in the title: are you asking how to do this in LINQ not SQL?
– Rup
Nov 8 at 9:41
1
Put the (formatted) SQL in your question please, not in the comments, @mr.jm
– Larnu
Nov 8 at 9:42
1
1
What have to tried so far, can you post your attempt SQL please? What were the errors you got, or what was wrong with the results? Stack Overflow isn't a free coding service, it's for asking question for specific coding problems. You need to show us your code first for us to be able to help you fix it.
– Larnu
Nov 8 at 9:36
What have to tried so far, can you post your attempt SQL please? What were the errors you got, or what was wrong with the results? Stack Overflow isn't a free coding service, it's for asking question for specific coding problems. You need to show us your code first for us to be able to help you fix it.
– Larnu
Nov 8 at 9:36
@Larnu im using this query select min(a.ProdName), max(b.Quantity),max(b.Size),min(b.Reference) from tblProd a INNER JOIN tblStock b ON a.ID = b.ProdID
– mr.jm
Nov 8 at 9:38
@Larnu im using this query select min(a.ProdName), max(b.Quantity),max(b.Size),min(b.Reference) from tblProd a INNER JOIN tblStock b ON a.ID = b.ProdID
– mr.jm
Nov 8 at 9:38
@Larnu also thi query but I can't show the load 1,2,3 select min(a.ProdName), max(b.Quantity) from tblProd a INNER JOIN tblStock b ON a.ID = b.ProdID where b.TranStatus = 'IN' and b.Reference = '0' group by a.ProdName,b.Credat
– mr.jm
Nov 8 at 9:41
@Larnu also thi query but I can't show the load 1,2,3 select min(a.ProdName), max(b.Quantity) from tblProd a INNER JOIN tblStock b ON a.ID = b.ProdID where b.TranStatus = 'IN' and b.Reference = '0' group by a.ProdName,b.Credat
– mr.jm
Nov 8 at 9:41
I'd guess you could use ROW NUMBER OVER PARTITION to put tblstock into order and get a load number for each row, then PIVOT to turn that into the table you want. But I'm not an expert here. Or you've put C# in the title: are you asking how to do this in LINQ not SQL?
– Rup
Nov 8 at 9:41
I'd guess you could use ROW NUMBER OVER PARTITION to put tblstock into order and get a load number for each row, then PIVOT to turn that into the table you want. But I'm not an expert here. Or you've put C# in the title: are you asking how to do this in LINQ not SQL?
– Rup
Nov 8 at 9:41
1
1
Put the (formatted) SQL in your question please, not in the comments, @mr.jm
– Larnu
Nov 8 at 9:42
Put the (formatted) SQL in your question please, not in the comments, @mr.jm
– Larnu
Nov 8 at 9:42
|
show 4 more comments
1 Answer
1
active
oldest
votes
up vote
3
down vote
accepted
You can try to use ROW_NUMBER window function make row number for tblstock table.
then use condition aggregate function to make pivot.
SELECT ProdName,
[1st load],
[2st load],
[3st load],
[4st load],
price,
(([1st load]+[2st load] +[3st load]+[4st load]) * price) 'Total'
FROM (
SELECT t1.ProdName,
MAX(CASE WHEN t2.rn = 1 then t2.Quantity ELSE 0 END) '1st load',
MAX(CASE WHEN t2.rn = 2 then t2.Quantity ELSE 0 END) '2st load',
MAX(CASE WHEN t2.rn = 3 then t2.Quantity ELSE 0 END) '3st load',
MAX(CASE WHEN t2.rn = 4 then t2.Quantity ELSE 0 END) '4st load',
t1.Price
FROM tblProduct t1
LEFT JOIN (
SELECT *,ROW_NUMBER() over(partition by ProdName order by INdate desc) rn
FROM tblstock
) t2 on t1.ProdName = t2.ProdName
GROUP BY t1.ProdName,t1.Price
)t1
answered Nov 8 at 9:44
D-Shih
23.7k61331
It work a bit..my move now to modify my query..thank you very much for the idea!
– mr.jm
Nov 8 at 9:52
Sir, I already edited the tables question... how about if i add column in tblStock which indicate the out of item to 1 column..Please see sample in question. Thanks
– mr.jm
Nov 8 at 10:41
1
You probably just have to ignore the out rows in the left join for the partition and then either add a simpler equivalent left join that returns out rows only and then show rn = 1 from that table in the out column, or if there can only ever be one then a simpler left join that just matches any out row. Hopefully you can see how to do this yourself!
– Rup
Nov 8 at 11:56
1
@rup Thanks for the feedback..i understand now the sql query.. i can now modify it..
– mr.jm
Nov 8 at 12:38
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
accepted
You can try to use ROW_NUMBER window function make row number for tblstock table.
then use condition aggregate function to make pivot.
SELECT ProdName,
[1st load],
[2st load],
[3st load],
[4st load],
price,
(([1st load]+[2st load] +[3st load]+[4st load]) * price) 'Total'
FROM (
SELECT t1.ProdName,
MAX(CASE WHEN t2.rn = 1 then t2.Quantity ELSE 0 END) '1st load',
MAX(CASE WHEN t2.rn = 2 then t2.Quantity ELSE 0 END) '2st load',
MAX(CASE WHEN t2.rn = 3 then t2.Quantity ELSE 0 END) '3st load',
MAX(CASE WHEN t2.rn = 4 then t2.Quantity ELSE 0 END) '4st load',
t1.Price
FROM tblProduct t1
LEFT JOIN (
SELECT *,ROW_NUMBER() over(partition by ProdName order by INdate desc) rn
FROM tblstock
) t2 on t1.ProdName = t2.ProdName
GROUP BY t1.ProdName,t1.Price
)t1
answered Nov 8 at 9:44
D-Shih
23.7k61331
It work a bit..my move now to modify my query..thank you very much for the idea!
– mr.jm
Nov 8 at 9:52
Sir, I already edited the tables question... how about if i add column in tblStock which indicate the out of item to 1 column..Please see sample in question. Thanks
– mr.jm
Nov 8 at 10:41
1
You probably just have to ignore the out rows in the left join for the partition and then either add a simpler equivalent left join that returns out rows only and then show rn = 1 from that table in the out column, or if there can only ever be one then a simpler left join that just matches any out row. Hopefully you can see how to do this yourself!
– Rup
Nov 8 at 11:56
1
@rup Thanks for the feedback..i understand now the sql query.. i can now modify it..
– mr.jm
Nov 8 at 12:38
add a comment |
up vote
3
down vote
accepted
You can try to use ROW_NUMBER window function make row number for tblstock table.
then use condition aggregate function to make pivot.
SELECT ProdName,
[1st load],
[2st load],
[3st load],
[4st load],
price,
(([1st load]+[2st load] +[3st load]+[4st load]) * price) 'Total'
FROM (
SELECT t1.ProdName,
MAX(CASE WHEN t2.rn = 1 then t2.Quantity ELSE 0 END) '1st load',
MAX(CASE WHEN t2.rn = 2 then t2.Quantity ELSE 0 END) '2st load',
MAX(CASE WHEN t2.rn = 3 then t2.Quantity ELSE 0 END) '3st load',
MAX(CASE WHEN t2.rn = 4 then t2.Quantity ELSE 0 END) '4st load',
t1.Price
FROM tblProduct t1
LEFT JOIN (
SELECT *,ROW_NUMBER() over(partition by ProdName order by INdate desc) rn
FROM tblstock
) t2 on t1.ProdName = t2.ProdName
GROUP BY t1.ProdName,t1.Price
)t1
answered Nov 8 at 9:44
D-Shih
23.7k61331
It work a bit..my move now to modify my query..thank you very much for the idea!
– mr.jm
Nov 8 at 9:52
Sir, I already edited the tables question... how about if i add column in tblStock which indicate the out of item to 1 column..Please see sample in question. Thanks
– mr.jm
Nov 8 at 10:41
1
You probably just have to ignore the out rows in the left join for the partition and then either add a simpler equivalent left join that returns out rows only and then show rn = 1 from that table in the out column, or if there can only ever be one then a simpler left join that just matches any out row. Hopefully you can see how to do this yourself!
– Rup
Nov 8 at 11:56
1
@rup Thanks for the feedback..i understand now the sql query.. i can now modify it..
– mr.jm
Nov 8 at 12:38
add a comment |
up vote
3
down vote
accepted
up vote
3
down vote
accepted
You can try to use ROW_NUMBER window function make row number for tblstock table.
then use condition aggregate function to make pivot.
SELECT ProdName,
[1st load],
[2st load],
[3st load],
[4st load],
price,
(([1st load]+[2st load] +[3st load]+[4st load]) * price) 'Total'
FROM (
SELECT t1.ProdName,
MAX(CASE WHEN t2.rn = 1 then t2.Quantity ELSE 0 END) '1st load',
MAX(CASE WHEN t2.rn = 2 then t2.Quantity ELSE 0 END) '2st load',
MAX(CASE WHEN t2.rn = 3 then t2.Quantity ELSE 0 END) '3st load',
MAX(CASE WHEN t2.rn = 4 then t2.Quantity ELSE 0 END) '4st load',
t1.Price
FROM tblProduct t1
LEFT JOIN (
SELECT *,ROW_NUMBER() over(partition by ProdName order by INdate desc) rn
FROM tblstock
) t2 on t1.ProdName = t2.ProdName
GROUP BY t1.ProdName,t1.Price
)t1
answered Nov 8 at 9:44
D-Shih
23.7k61331
You can try to use ROW_NUMBER window function make row number for tblstock table.
then use condition aggregate function to make pivot.
SELECT ProdName,
[1st load],
[2st load],
[3st load],
[4st load],
price,
(([1st load]+[2st load] +[3st load]+[4st load]) * price) 'Total'
FROM (
SELECT t1.ProdName,
MAX(CASE WHEN t2.rn = 1 then t2.Quantity ELSE 0 END) '1st load',
MAX(CASE WHEN t2.rn = 2 then t2.Quantity ELSE 0 END) '2st load',
MAX(CASE WHEN t2.rn = 3 then t2.Quantity ELSE 0 END) '3st load',
MAX(CASE WHEN t2.rn = 4 then t2.Quantity ELSE 0 END) '4st load',
t1.Price
FROM tblProduct t1
LEFT JOIN (
SELECT *,ROW_NUMBER() over(partition by ProdName order by INdate desc) rn
FROM tblstock
) t2 on t1.ProdName = t2.ProdName
GROUP BY t1.ProdName,t1.Price
)t1
answered Nov 8 at 9:44
D-Shih
23.7k61331
edited Nov 8 at 9:45
answered Nov 8 at 9:44
D-Shih
23.7k61331
answered Nov 8 at 9:44
D-Shih
23.7k61331
answered Nov 8 at 9:44
D-Shih
23.7k61331
23.7k61331
It work a bit..my move now to modify my query..thank you very much for the idea!
– mr.jm
Nov 8 at 9:52
Sir, I already edited the tables question... how about if i add column in tblStock which indicate the out of item to 1 column..Please see sample in question. Thanks
– mr.jm
Nov 8 at 10:41
1
You probably just have to ignore the out rows in the left join for the partition and then either add a simpler equivalent left join that returns out rows only and then show rn = 1 from that table in the out column, or if there can only ever be one then a simpler left join that just matches any out row. Hopefully you can see how to do this yourself!
– Rup
Nov 8 at 11:56
1
@rup Thanks for the feedback..i understand now the sql query.. i can now modify it..
– mr.jm
Nov 8 at 12:38
add a comment |
It work a bit..my move now to modify my query..thank you very much for the idea!
– mr.jm
Nov 8 at 9:52
Sir, I already edited the tables question... how about if i add column in tblStock which indicate the out of item to 1 column..Please see sample in question. Thanks
– mr.jm
Nov 8 at 10:41
1
You probably just have to ignore the out rows in the left join for the partition and then either add a simpler equivalent left join that returns out rows only and then show rn = 1 from that table in the out column, or if there can only ever be one then a simpler left join that just matches any out row. Hopefully you can see how to do this yourself!
– Rup
Nov 8 at 11:56
1
@rup Thanks for the feedback..i understand now the sql query.. i can now modify it..
– mr.jm
Nov 8 at 12:38
It work a bit..my move now to modify my query..thank you very much for the idea!
– mr.jm
Nov 8 at 9:52
It work a bit..my move now to modify my query..thank you very much for the idea!
– mr.jm
Nov 8 at 9:52
Sir, I already edited the tables question... how about if i add column in tblStock which indicate the out of item to 1 column..Please see sample in question. Thanks
– mr.jm
Nov 8 at 10:41
Sir, I already edited the tables question... how about if i add column in tblStock which indicate the out of item to 1 column..Please see sample in question. Thanks
– mr.jm
Nov 8 at 10:41
1
1
You probably just have to ignore the out rows in the left join for the partition and then either add a simpler equivalent left join that returns out rows only and then show rn = 1 from that table in the out column, or if there can only ever be one then a simpler left join that just matches any out row. Hopefully you can see how to do this yourself!
– Rup
Nov 8 at 11:56
You probably just have to ignore the out rows in the left join for the partition and then either add a simpler equivalent left join that returns out rows only and then show rn = 1 from that table in the out column, or if there can only ever be one then a simpler left join that just matches any out row. Hopefully you can see how to do this yourself!
– Rup
Nov 8 at 11:56
1
1
@rup Thanks for the feedback..i understand now the sql query.. i can now modify it..
– mr.jm
Nov 8 at 12:38
@rup Thanks for the feedback..i understand now the sql query.. i can now modify it..
– mr.jm
Nov 8 at 12:38
add a comment |
draft saved
draft discarded
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
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53204927%2fplease-help-sql-in-visual-studio-c-sharp%23new-answer', 'question_page');
}
);
Post as a guest
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
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
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
1
What have to tried so far, can you post your attempt SQL please? What were the errors you got, or what was wrong with the results? Stack Overflow isn't a free coding service, it's for asking question for specific coding problems. You need to show us your code first for us to be able to help you fix it.
– Larnu
Nov 8 at 9:36
@Larnu im using this query select min(a.ProdName), max(b.Quantity),max(b.Size),min(b.Reference) from tblProd a INNER JOIN tblStock b ON a.ID = b.ProdID
– mr.jm
Nov 8 at 9:38
@Larnu also thi query but I can't show the load 1,2,3 select min(a.ProdName), max(b.Quantity) from tblProd a INNER JOIN tblStock b ON a.ID = b.ProdID where b.TranStatus = 'IN' and b.Reference = '0' group by a.ProdName,b.Credat
– mr.jm
Nov 8 at 9:41
I'd guess you could use ROW NUMBER OVER PARTITION to put tblstock into order and get a load number for each row, then PIVOT to turn that into the table you want. But I'm not an expert here. Or you've put C# in the title: are you asking how to do this in LINQ not SQL?
– Rup
Nov 8 at 9:41
1
Put the (formatted) SQL in your question please, not in the comments, @mr.jm
– Larnu
Nov 8 at 9:42