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

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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sqlcurious

ASKER

thanks