Avatar of sqlcurious
sqlcurious
Flag for United States of America asked on

data to appear in single rows avoiding nulls

Hi experts below is the data I got after stripping commas and inserting data column at a time :
TABLE 1
id      Customer_No      Inventory_No      Price      Qty
1      1022                       98830057                   NULL      NULL
2      1022                      98830064                   NULL      NULL
3      1022                      NULL                   60.00      NULL
4      1022                      NULL                  90.00      NULL
5      1022                      NULL                  NULL      1
6      1022                      NULL                  NULL      1


But I would need data in the format

id      Customer_No      Inventory_No      Price      Qty
1      1022                       98830000                   60.00      1
2      1022                      98830001                   90.00      1

Please help!
Attached is the file that I used to insert in table 1
QUERY.sql
Microsoft SQL Server 2008MySQL Server

Avatar of undefined
Last Comment
sqlcurious

8/22/2022 - Mon
lcohan

CREATE TABLE #Inventory
  (id int identity(1,1),
  Customer_No int NULL,
  Inventory_No int NULL,
  Price money NULL,
  Qty int NULL);
 
INSERT INTO #Inventory (Customer_No,Inventory_No,Price,Qty)
SELECT 1022,98830000,60.00,1;


INSERT INTO #Inventory (Customer_No,Inventory_No,Price,Qty)
SELECT 1022,98830000,90.00,1;

SELECT * FROM #Inventory;
sqlcurious

ASKER
Hi Icohan, please see the file I attached, my data is extracted from a csv file, so the following columns will be separated with commas, this is just a sample data, there will be a bigger string for each
Inventory_No='98830000,98830001,'
  Price='60.00,90.00,'
  Qty='1,1,'
Customer_No =1022

I was using the split function to split these and then insert, not sure how I can split all the columns at the same time so that I get the following results
id      Customer_No      Inventory_No      Price      Qty
1      1022                       98830000                   60.00      1
2      1022                      98830001                   90.00      1
John_Vidmar

You only have one attachment (query.sql), there is no sample CSV file.  You may have to correct query.sql because it's currently inserting related-data into several non-related rows.  Therefore, you wouldn't be able to associate the appropriate quantity-to-price, and price-to-inventory.  Would you be able to do the following:
DECLARE	@p001_Inventory_No	varchar(MAX)
,	@p002_Price		varchar(MAX)
,	@p003_Qty		varchar(MAX)
,	@p004_Customer_No	int

SET @p001_Inventory_No	='98830000,98830001,'
SET @p002_Price		='60.00,90.00,'
SET @p003_Qty		='1,1,'
SET @p004_Customer_No	=1022
  
----------- DROP TABLE #inventory 
CREATE TABLE #Inventory 
(	id		int identity(1,1)
,	Customer_No	int
,	Inventory_No	int
,	Price		money
,	Qty		int
)

INSERT	#Inventory 
(	Customer_No
,	Inventory_No
,	Price
,	Qty
)
SELECT	Customer_No	= @p004_Customer_No 
,	Inventory_No	= (SELECT value FROM H.dbo.SplitWords(@p001_Inventory_No))
,	Price		= (SELECT value FROM H.dbo.SplitWords(@p002_price))
,	Qty		= (SELECT value FROM H.dbo.SplitWords(@p003_Qty))

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
PortletPaul

You need to "transpose" the csv data before placing it into a table.

98830000,98830001
60.00,90.00
1,1

Trans[pose that to:

98830000,60.00,1
98830001,90.00,1


SSIS can be used to do this
This blog (a bit old, 2007) has a step by step discussion of the same need.
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sqlcurious

ASKER
thanks