Create a New Column Based on Values from Other Columns in Table

Question

I am trying to create new columns labelled as the values in "stno" with each containing its respective value in "qyhnd" and keeping it in order by "pano20." So if pano20 is 6Y9807, and stno is 02, and qyhnd is 6, then my new column labelled 02 should have the value of 6 for pano20 6Y9807. The examples of what I currently have and what I am trying to achieve are below. Thanks

What I have now:

PANO20	QYHND	STNO
6Y9807	6	02
6N3191	8	01
1476605	15	02
1672190	6	01
2641440	2	00

I am trying to get:

PANO20	QYHND	STNO	00	01	02
6Y9807	6	02			6
6N3191	8	01		8
1476605	15	02			15
1672190	6	01		6
2641440	2	00	2

This is my attempt:

select ps.PANO20, ps.SOS1, ps.QYHND, ps.QYOR, ps.MIN, ps.MAX, ps.STNO, ps.SKNSKI, ms.SOS1, ms.UNCS, SUM (DMMO01+ DMMO02+ DMMO03+ DMMO04+ DMMO05+ DMMO06+ DMMO07+ DMMO08+ DMMO09+ DMMO10+ DMMO11+ DMMO12) as [Demand12]
from LIBH07.dbo.PCPPIST0 ps 
inner join LIBCOM500.dbo.PCPPRMS0 ms
on ps.PANO20 = ms.PANO20
where ps.STNO IN  ('00','01','02','03') and ps.SOS1 = '000' and ms.SOS1 = '000' 
group by ps.SOS1, ps.PANO20, qyhnd, qyor, MIN, MAX, stno, sknski, ms.sos1, ms.UNCS
order by PANO20 desc

create table OnHandStore
as select PANO20,
SUM(case when stno = '00' then qyhnd end) as Henderson,
SUM(case when stno = '01' then qyhnd end) as Reno, 
SUM(case when stno = '02' then qyhnd end) as Elko,
SUM(case when stno = '03' then qyhnd end) as Winnemucca, 
from pcppist0 as t1
group by PANO20;

Have a look at [`CASE`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver16). — Thom A, Jun 16 '23 at 14:57
@ThomA I am trying to add what sql I have currently but it's not allowing me to I did have case though let me see if I'm able to add it as text as it won't allow me via code format — lberecek, Jun 16 '23 at 15:27
`CREATE TABLE...AS` isn't valid T-SQL; that looks like MySQL. — Thom A, Jun 16 '23 at 15:58
@ThomA Got it, okay so I am just looking to add the columns to the current joined table. What would be the correct statement? — lberecek, Jun 16 '23 at 16:01
I didn't see that you added anything I apologize I had a lot of tabs open and may have done it by accident @ThomA — lberecek, Jun 16 '23 at 16:02
if you want create view ,you must use "Create view " instead of "Create table" and Remove "," at end "create view OnHandStore as select PANO20, SUM(case when stno = '00' then qyhnd end) as Henderson, SUM(case when stno = '01' then qyhnd end) as Reno, SUM(case when stno = '02' then qyhnd end) as Elko, SUM(case when stno = '03' then qyhnd end) as Winnemucca from pcppist0 as t1 group by PANO20;" — abolfazl sadeghi, Jun 16 '23 at 16:05
for first query ,i need structure and example data and expected to solve your problem — abolfazl sadeghi, Jun 16 '23 at 16:09
So I am joining two tables by part number, after the join, I need to add the columns referenced in my initial question. All of the columns needed to form this come from the original table pcppist0. Here is the new query I have, and I get an error of "Incorrect syntax near the keyword 'into'." Do I need to do this before the join of the two tables pcppist0 and pcpprms0? — lberecek, Jun 16 '23 at 16:36
if whole columns use is pcppist0 table,you can subquery(my query) insted of original pcppist,if you want insert to other table ,you must use into before select "select * into [NewTable] from" — abolfazl sadeghi, Jun 16 '23 at 17:16

score 0 · Answer 1 · edited Jun 16 '23 at 22:52

0

As @ThomA said you can do this with a case expression

select 
         PANO20
         ,QYHND
         ,STNO
         ,(case when STNO='00' then QYHND else '' end ) as '00'
        ,(case when STNO='01' then QYHND else '' end ) as '01'
        ,(case when STNO='02' then QYHND else '' end ) as '02'
from TT

Demo

edited Jun 16 '23 at 22:52

Dale K

25,246
15
42
71

answered Jun 16 '23 at 15:32

abolfazl sadeghi

2,277
2
12
20

This is very similar to the code I currently have. Do I need a "group by" pano20; at the end? – lberecek Jun 16 '23 at 15:34
Also this is a merged table, so when I am selecting "from" how to I specify the current set? – lberecek Jun 16 '23 at 15:41
For your example, you don't need group, but you need to group, you can use group by(if you have new record "2641440" need show only 1 record ,you need group by ),if you need group by must specify QYHND is sum or max or min – abolfazl sadeghi Jun 16 '23 at 15:41
I am going to add the sql I have by asking a new question and will reference it here. I keep getting errors that I can not edit my question. – lberecek Jun 16 '23 at 15:45
if you two or multi table ,you must use join "please show query that i can help you",if you union ,you use subquery after use my query"select *,case ... from( select PANO20,QYHND,STNO from t union select PANO20,QYHND,STNO from t2 )" – abolfazl sadeghi Jun 16 '23 at 15:46
Here is what I have currently https://dbfiddle.uk/JTegPpV2 – lberecek Jun 16 '23 at 15:58

nbk · Answer 2 · 2023-06-16T19:23:56.747

0

You can use aggregation or pivot

CREATE TABLE pcppist0
    (PANO20 varchar(7), [QYHND] int, [stno] varchar(2))
;
    
INSERT INTO pcppist0
    (PANO20, QYHND, stno)
VALUES
    ('6Y9807', 6, '02'),
    ('6N3191', 8, '01'),
    ('1476605', 15, '02'),
    ('1672190', 6, '01'),
    ('2641440', 2, '00')
;

5 rows affected

 select PANO20,

   SUM(case when stno = '00' then qyhnd ELSE 0 end)  as Henderson,
SUM(case when stno = '01' then qyhnd ELSE 0 end) as Reno, 
SUM(case when stno = '02' then qyhnd ELSE 0 end) as Elko,
SUM(case when stno = '03' then qyhnd ELSE 0 end) as Winnemucca 
   INTO OnHandStore
from pcppist0 as t1
group by PANO20;

5 rows affected

  SELECT * FROM OnHandStore

PANO20	Henderson	Reno	Elko
1476605	0	0	15
1672190	0	6	0
2641440	2	0	0
6N3191	0	8	0
6Y9807	0	0	6

 select PANO20,
   [00] As Henderson, [01] as Reno , [02] as  Elko, [03] as Winnemucca
   INTO OnHandStore1
   from pcppist0 as t1
PIVOT  
(  
  MAX(qyhnd)  
  FOR stno IN ([00], [01], [02], [03])  
) AS PivotTable;

5 rows affected

  SELECT * FROM OnHandStore1

PANO20	Henderson	Reno	Elko	Winnemucca
1476605	null	null	15	null
1672190	null	6	null	null
2641440	2	null	null	null
6N3191	null	8	null	null
6Y9807	null	null	6	null

fiddle

edited Jun 16 '23 at 19:23

answered Jun 16 '23 at 15:32

nbk

45,398
8
30
47

Only problem with this is that I have about 400k "pano20's" so I can't specify as you did in the first section – lberecek Jun 16 '23 at 15:40
200000 are no problem 2 mio also no problem, with more st_no you would get a problem, as you would need to define all in a max statementm but then you have to use dynamic sql like https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – nbk Jun 16 '23 at 15:44
I am going to add the sql I have by asking a new question and will reference it here. I keep getting errors that I can not edit my question. – lberecek Jun 16 '23 at 15:45
don't forget to delete this one – nbk Jun 16 '23 at 15:57
Its okay, I just did a fiddle please look here https://dbfiddle.uk/JTegPpV2 – lberecek Jun 16 '23 at 15:58
Sum is more difficult as NULL can not be summed see https://dbfiddle.uk/WnuAEcb4 and ou have a typo – nbk Jun 16 '23 at 16:08
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/254106/discussion-between-lberecek-and-nbk). – lberecek Jun 16 '23 at 16:38

Create a New Column Based on Values from Other Columns in Table

2 Answers2