Flat one line per customer output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Flat one line per customer output

Author

Topic

mlmeyer
Starting Member

26 Posts

Posted - 2005-02-01 : 10:12:02

Hi All, I need to take a database table that lists CustomerIDs, Product and a Product Code and make it into a flat file in which the CustomerID only appears on one line followed by the Products and Codes. So, for example, I would like to take the following data:

CustID....Product....Code
123.......Apples......7766
123.......Oranges....2233
345.......Kiwi..........8899
567.......Oranges....6633
567.......Bananas....1122
567.......Apples......7722

and have it appear as a flat file as shown below with each CustomerID on only one line:

CustID....Product1...Code1...Product2...Code2...Product3....Code3
123.......Apples.......7766.....Oranges.....2233
345.......Kiwi...........8899
567.......Oranges.....6633.....Bananas....1122.....Apples.......7722

Does anyone know of a way to handle this?

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-02-01 : 10:17:56

I don't think what you posted is possible. Or if it is possible it would be pretty weird dynamic sql.

You could put the product and code inside of a CSV string and your result set would be two columns CustID and the CSV string. If this will work for you try searching CSV on this site and you'll find some nifty articles.

Dustin Michaels

mlmeyer
Starting Member

26 Posts

Posted - 2005-02-01 : 11:06:26

Would it make a difference (or be any easier) if I combined the Product and Product Code into one field as shown here:

CustID...ProductCode
123.......Apples-7766
123.......Oranges-2233
345.......Kiwi-8899
567.......Oranges-6633
567.......Bananas-1122
567.......Apples-7722

And then tried to output it to like this:

CustID..ProdCode1.......ProdCode2..........ProdCode3
123.....Apples-7766......Oranges-2233
345.....Kiwi-8899
567.....Oranges-6633....Bananas-1122.....Apples-7722

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-02-01 : 11:23:25

I don't think so. The problem is that you have a dynamic number of columns in your result set. Is there a reason why you can't use a CSV string and parse the results in your application?

Dustin Michaels

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-01 : 11:29:59

MLMEYER,

I used this for the table:

CustID Product Code
123 Apples 5566
544 Celery 2222
778 Cinnamon 0009
811 Ginger Ale 4000
123 Oranges 7788
544 Pumpkins 1111
I expanded on types of items for testing.

This is the code I created,

SELECT a.CUSTID, a.PRODUCT, a.CODE
FROM TESTTAB a
;
SELECT a.CUSTID, a.PRODUCT, a.CODE,
b.CUSTID as CUSTID2, b.PRODUCT as PRODUCT2, b.CODE as CODE2
FROM TESTTAB a,
TESTTAB b
WHERE (a.CUSTID = b.CUSTID) and (A.PRODUCT is NOT NULL) and a.CODE is NOT NULL
;

and this resulted in:

123 Apples 5566 123 Apples 5566
123 Apples 5566 123 Oranges 7788
544 Celery 2222 544 Celery 2222
544 Celery 2222 544 Pumpkins 1111
778 Cinnamon 0009 778 Cinnamon 0009
811 Ginger Ale 4000 811 Ginger Ale 4000
123 Oranges 7788 123 Apples 5566
123 Oranges 7788 123 Oranges 7788
544 Pumpkins 1111 544 Celery 2222
544 Pumpkins 1111 544 Pumpkins 1111

Of course you'll have to remove the duplicates.

This isn't a full solution, but this should give you a start.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

MuffinMan
Posting Yak Master

107 Posts

Posted - 2005-02-01 : 12:42:19

How about this?


set nocount on

create table #tmp (CustID int not null , Product varchar(50) not null, Code int not null)

insert #tmp select 123, 'Apples', 7766 
insert #tmp select 123, 'Oranges', 2233 
insert #tmp select 345, 'Kiwi', 8899 
insert #tmp select 567, 'Oranges', 6633 
insert #tmp select 567, 'Bananas', 1122 
insert #tmp select 567, 'Apples', 7722 

-- select * from #tmp

select top 1 CustID, count(distinct Product) as MaxProducts
into #products
from #tmp
group by CustID
order by count(distinct Product) desc  /** orginially had count(*) desc */

create table #final (CustID int not null PRIMARY KEY CLUSTERED)

declare @MaxProducts int
select @MaxProducts = MaxProducts from #products

declare @loop int, @sql varchar(2000)
select @loop = 0

while @loop < @MaxProducts
begin
	select @loop = @loop + 1
	select @sql = 'Product' + convert(varchar(10), @loop) + ' varchar(50) null '
	select @sql = @sql + ', Code' + convert(varchar(10), @loop) + ' int null '

	select @sql = 'alter table #final add ' + @sql
	--print @sql
	exec(@sql)

end

create table #records (
	RowID int identity(1,1) not null PRIMARY KEY CLUSTERED
	, CustID int not null 
	, Product varchar(50) not null
	, Code int not null
)

insert #records (CustID, Product, Code)
select CustID, Product, Code
from #tmp
order by CustID, Product, Code


declare @MaxRecs int
select @MaxRecs = count(*) from #records

declare @CustID int, @LastCustID int, @Product varchar(50), @LastProduct varchar(50)
	, @Code int, @LastCode int, @ProductLoop int
select @ProductLoop = 0, @CustID  = 0, @LastCustID = 0, @Product = '', @LastProduct = ''
	, @Code = 0, @LastCode = 0


--initialize the first record
select @LastCustID = CustID, @LastProduct = Product, @LastCode = Code from #records where RowID = 1
select @loop = 0, @sql = ''

while @loop < @MaxRecs
begin
	select @loop = @loop + 1
	select @ProductLoop = @ProductLoop + 1

	select @CustID = CustID, @Product = Product, @Code = Code from #records where RowID = @loop


	if @CustID = @LastCustID
		begin
			select @sql = @sql + ', ''' + @Product + ''', ' + convert(varchar(10), @Code) + ''
			--print @sql
		end
	else
		begin
			while @ProductLoop <= @MaxProducts
			begin
				--fill the remaining columns with NULL values
				select @sql = @sql + ', NULL, NULL'
				select @ProductLoop = @ProductLoop + 1
			end
			select @sql = 'select ' + convert(varchar(10), @LastCustID) + @sql
			--print @sql
			insert #final
			exec(@sql)
			select @ProductLoop = 1
			select @sql = ''

			select @LastCustID = @CustID, @lastProduct = @Product, @LastCode = @Code
			select @sql = @sql + ', ''' + @Product + ''', ' + convert(varchar(10), @Code) + ''

		end
	
	
end


--- write the last record
select @ProductLoop = @ProductLoop + 1
while @ProductLoop <= @MaxProducts
begin
	select @sql = @sql + ', NULL, NULL'
	select @ProductLoop = @ProductLoop + 1
end
select @sql = 'select ' + convert(varchar(10), @LastCustID) + @sql
--print @sql
insert #final
exec(@sql)


select * from #final

drop table #tmp, #products, #final, #records

****************************
** Here is the output **
****************************


CustID  Product1 Code1       Product2 Code2       Product3 Code3       
------- -------- ----------- -------- ----------- -------- ----------- 
123     Apples   7766        Oranges  2233        NULL     NULL
345     Kiwi     8899        NULL     NULL        NULL     NULL
567     Apples   7722        Bananas  1122        Oranges  6633

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-01 : 12:50:21

what's the most products a customer can have per line?

(if you are not sure, this query may help):

select top 1 custID, count(distinct ProductCode) as HighestProductCount
from YourData
group by CustID
order by count(distinct ProductCode) DESC

- Jeff

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-01 : 12:51:33

MuffinMan.......that is truly AWESOME!

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

MuffinMan
Posting Yak Master

107 Posts

Posted - 2005-02-01 : 12:56:38

AWESOME!?!?! Well, thanks.

Efficient? Who knows!?!? Practical? Maybe.

Just goes to show you can do almost anything in SQL!

(Thanks to jsmith8858 for pointing out the correct "ORDER BY" in the MaxProducts section)

mlmeyer
Starting Member

26 Posts

Posted - 2005-02-01 : 13:01:05

Just tried it and it works great! Thanks!!!

mlmeyer
Starting Member

26 Posts

Posted - 2005-02-01 : 16:23:45

One more question on this query. It works great for most of the Codes that are in the table but I have one Code that is alphanumeric (ex= A1234). I assume to handle this I will need to change the Code field from int to varchar but am having problems with it as I must not be changing this correctly in all places.

Can you let me know if you have any ideas of what needs to be changed? Thanks again.

MuffinMan
Posting Yak Master

107 Posts

Posted - 2005-02-02 : 10:01:16

Anywhere you see the field 'Code' defined, change the datatype from INT to varchar(10).

Also, this line needs to be altered:


select @sql = @sql + ', ''' + @Product + ''', ' + convert(varchar(10), @Code) + ''

You need to add addtional single quotes (') like this:


select @sql = @sql + ', ''' + @Product + ''', ''' + convert(varchar(10), @Code) + ''' '

Note: this line appears twice in the code

mlmeyer
Starting Member

26 Posts

Posted - 2005-02-02 : 12:26:35

I made the changes you noted but am now getting the error message:
"Server: Msg 245, Level 16, State 1, Line 68
Syntax error converting the varchar value 'A7766' to a column of data type int."

'A7766' is the name of a Code and line 68 of the query contains the following:

--initialize the first record
select @LastCustID = CustID, @lastProduct = Product,
@LastCode = Code from #records where RowID = 1
select @loop = 0, @sql = ''

Is there something that I failed to switch to varchar properly?

MuffinMan
Posting Yak Master

107 Posts

Posted - 2005-02-02 : 16:39:18

Did you change these lines? (around line 60):


declare @CustID int, @LastCustID int, @Product varchar(50), @LastProduct varchar(50)
	, @Code int, @LastCode int, @ProductLoop int
select @ProductLoop = 0, @CustID  = 0, @LastCustID = 0, @Product = '', @LastProduct = ''
	, @Code = 0, @LastCode = 0


declare @CustID int, @LastCustID int, @Product varchar(50), @LastProduct varchar(50)
	, @Code varchar(10), @LastCode varchar(10), @ProductLoop int
select @ProductLoop = 0, @CustID  = 0, @LastCustID = 0, @Product = '', @LastProduct = ''
	, @Code = '', @LastCode = ''

mlmeyer
Starting Member

26 Posts

Posted - 2005-02-04 : 11:28:10

That worked great. Thanks again!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources