We help IT Professionals succeed at work.

data  to appear in single rows avoiding nulls

209 Views
Last Modified: 2014-05-14
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
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
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;

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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.
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
thanks
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.