Concat. of rows - SQL Server Forums

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)

Concat. of rows

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-25 : 07:45:04

Dear All,

I have the following secnario,

table: temp
Cola colb Colc
100 taxA inv1
110 taxB inv1
120 taxC inv1

Now i need the three values in single line for the invoice no
like

Cola colb Colc
100 110 120 taxA taxB taxC inv1

Is it possible..?
Can any one please give me the idea
thanks and regards
krishnakumar.C

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-25 : 07:55:55

Yes.

-- Prepare test data
declare @temp table (Cola int, colb varchar(4), Colc varchar(4))

insert	@temp
select	100, 'taxA', 'inv1' union all
select	110, 'taxB', 'inv1' union all 
select	120, 'taxC', 'inv1'

-- Do the magic
select		x.water ColA,
		x.pepsi ColB,
		x.cola ColC,
		t1.colb + ' ' + t2.colb + ' ' + t3.colb + ' ' + x.drink Information
from		(
			select		colc drink,
					min(cola) water,
					sum(cola) - min(cola) - max(cola) pepsi,
					max(cola) cola
			from		@temp
			group by	colc
		) x
inner join	@temp t1 on t1.colc = x.drink and t1.cola = x.water
inner join	@temp t2 on t2.colc = x.drink and t2.cola = x.pepsi
inner join	@temp t3 on t3.colc = x.drink and t3.cola = x.cola

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-25 : 08:07:40

If you for some reason have not exactly 3 records for any colc, use this

-- Prepare test data
declare @temp table (Cola int, colb varchar(4), Colc varchar(4))

insert	@temp
select	100, 'taxA', 'inv1' union all
select	120, 'taxC', 'inv1' union all
select	100, 'taxA', 'inv2' union all
select	105, 'taxB', 'inv2' union all 
select	110, 'taxG', 'inv2' union all 
select	99, 'taxO', 'inv3' union all 
select	120, 'taxC', 'inv2'

-- Do the magic
select		x.water ColA,
		case when x.pepsi <= 0 or x.pepsi < x.water or x.pepsi > x.cola then null else x.pepsi end ColB,
		case when x.cola = x.water then null else x.cola end ColC,
		t1.colb + isnull(t2.colb + ' ', ' ') + case when x.cola = x.water then '' else t3.colb end + x.drink Information
from		(
			select		colc drink,
					min(cola) water,
					sum(cola) - min(cola) - max(cola) pepsi,
					max(cola) cola
			from		@temp
			group by	colc
		) x
inner join	@temp t1 on t1.colc = x.drink and t1.cola = x.water
left join	@temp t2 on t2.colc = x.drink and t2.cola = x.pepsi
inner join	@temp t3 on t3.colc = x.drink and t3.cola = x.cola

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-25 : 08:22:12

Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-25 : 14:18:47

No answer yet. Maybe he fell off his chair when he saw the simple solution...

Peter Larsson
Helsingborg, Sweden

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-26 : 05:47:03

Sorry Peter . I have just gone to client place now only i have checked ur solution. Great!! It's working

Thanks
Krishna

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-26 : 11:33:07

quote:
Originally posted by CSK

Sorry Peter . I have just gone to client place now only i have checked ur solution. Great!! It's working

Thanks
Krishna

You didnt answer to my question

Madhivanan

Failing to plan is Planning to fail

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-10-26 : 12:37:53

Love your column names... reminds me of my brother who used to name everything after Simpson's characters..

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-31 : 03:12:11

Dear Peter for the above query Does not return the third value

krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-31 : 03:33:03

Post your data here...

Peter Larsson
Helsingborg, Sweden

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-31 : 03:47:56

Tcd_temp table Data
829.89000000 Education Cess @ 2% of Serivice Tax 427 ARDI6/007337/BLR
41494.46000000 Service Tax @12% 427 ARDI6/007337/BLR
12000.00000000 Mobile Charges 427 ARDI6/000743/NCR
495.60000000 Education Cess @ 2% on Service Tax 427 ARDI6/000743/NCR
12000.00000000 Mobile Charges 427 ARDI6/000743/NCR
495.60000000 Education Cess @ 2% on Service Tax 427 ARDI6/000743/NCR
24780.00000000 Service Tax @12% 427 ARDI6/000743/NCR
12000.00000000 Mobile Charges 427 ARDI6/000743/NCR
495.60000000 Education Cess @ 2% on Service Tax 427 ARDI6/000743/NCR
24780.00000000 Service Tax @12% 427 ARDI6/000743/NCR
12000.00000000 Mobile Charges 427 ARDI6/000743/NCR
495.60000000 Education Cess @ 2% on Service Tax 427 ARDI6/000743/NCR
24780.00000000 Service Tax @12% 427 ARDI6/000743/NCR
12000.00000000 Mobile Charges 427 ARDI6/000743/NCR
495.60000000 Education Cess @ 2% on Service Tax 427 ARDI6/000743/NCR
24780.00000000 Service Tax @12% 427 ARDI6/000743/NCR
12000.00000000 Mobile Charges 427 ARDI6/000743/NCR
495.60000000 Education Cess @ 2% on Service Tax 427 ARDI6/000743/NCR
24780.00000000 Service Tax @12% 427 ARDI6/000743/NCR
12000.00000000 Mobile Charges 427 ARDI6/000743/NCR
495.60000000 Education Cess @ 2% on Service Tax 427 ARDI6/000743/NCR
24780.00000000 Service Tax @12% 427 ARDI6/000743/NCR
495.60000000 Education Cess @ 2% on Service Tax 427 ARDI6/000743/NCR
12000.00000000 Mobile Charges 427 ARDI6/000743/NCR
495.60000000 Education Cess @ 2% on Service Tax 427 ARDI6/000743/NCR
24780.00000000 Service Tax @12% 427 ARDI6/000743/NCR
12000.00000000 Mobile Charges 427 ARDI6/000743/NCR
24780.00000000 Service Tax @12% 427 ARDI6/000743/NCR
71.13000000 Education Cess @ 2% on Service Tax 427 ARDI6/000719/NCR
3556.56000000 Service Tax @12% 427 ARDI6/000719/NCR
12000.00000000 Mobile Charges 427 ARDI6/000743/NCR
495.60000000 Education Cess @ 2% on Service Tax 427 ARDI6/000743/NCR
12000.00000000 Mobile Charges 427 ARDI6/000743/NCR
495.60000000 Education Cess @ 2% on Service Tax 427 ARDI6/000743/NCR
24780.00000000 Service Tax @12% 427 ARDI6/000743/NCR
24780.00000000 Service Tax @12% 427 ARDI6/000743/NCR
24780.00000000 Service Tax @12% 427 ARDI6/000743/NCR
our Query
select Distinct
Convert(varchar,Round(Convert(numeric(18,2),t3.tcd_amt),0)) + Char(10) + isnull(Convert(varChar,Round(Convert(numeric(18,2),t2.tcd_amt),0)) + Char(10), '') + convert(varchar,round(Convert(numeric(18,2),t1.tcd_amt),0)) as TCD_AMT,
Convert(varChar(500),t3.tcd_desc) + Char(10)+ isnull(Convert(varchar(500),t2.tcd_desc) + Char(10), '') + convert(varchar(500),t1.tcd_desc) as TCD_DESC ,
Convert(varchar,t1.inv_no) as INV_NO
from (
select inv_no inv_no ,
min(Tcd_Amt) A,
sum(Tcd_Amt) - min(Tcd_Amt) - max(Tcd_Amt) B,
max(tcd_amt) C
from tcd_temp
Where Inv_no Between 'ARDI6/000743/NCR' And 'ARDI6/000743/NCR'
aND hOST_iD = '427'
group by inv_no, tcd_type

) x
Inner join tcd_temp t1 on t1.inv_no = x.inv_no and t1.tcd_amt = x.A
left join tcd_temp t2 on t2.inv_no = x.inv_no and t2.tcd_amt = x.B
Inner join tcd_temp t3 on t3.inv_no = x.inv_no and t3.tcd_amt = x.C
Where x.Inv_no Between 'ARDI6/000743/NCR' And 'ARDI6/000743/NCR'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-31 : 04:12:44

[code]-- prepare test data
declare @tcd_temp table (tcd_amt smallmoney, tcd_desc varchar(200), host_id int, inv_no varchar(100))

insert @tcd_temp
select 829.89000000, 'Education Cess @ 2% of Service Tax', 427, 'ARDI6/007337/BLR' union all
select 41494.46000000, 'Service Tax @12%', 427, 'ARDI6/007337/BLR' union all
select 12000.00000000, 'Mobile Charges', 427, 'ARDI6/000743/NCR' union all
select 495.60000000, 'Education Cess @ 2% on Service Tax', 427, 'ARDI6/000743/NCR' union all
select 12000.00000000, 'Mobile Charges', 427, 'ARDI6/000743/NCR' union all
select 495.60000000, 'Education Cess @ 2% on Service Tax', 427, 'ARDI6/000743/NCR' union all
select 24780.00000000, 'Service Tax @12%', 427, 'ARDI6/000743/NCR' union all
select 12000.00000000, 'Mobile Charges', 427, 'ARDI6/000743/NCR' union all
select 495.60000000, 'Education Cess @ 2% on Service Tax', 427, 'ARDI6/000743/NCR' union all
select 24780.00000000, 'Service Tax @12%', 427, 'ARDI6/000743/NCR' union all
select 12000.00000000, 'Mobile Charges', 427, 'ARDI6/000743/NCR' union all
select 495.60000000, 'Education Cess @ 2% on Service Tax', 427, 'ARDI6/000743/NCR' union all
select 24780.00000000, 'Service Tax @12%', 427, 'ARDI6/000743/NCR' union all
select 12000.00000000, 'Mobile Charges', 427, 'ARDI6/000743/NCR' union all
select 495.60000000, 'Education Cess @ 2% on Service Tax', 427, 'ARDI6/000743/NCR' union all
select 24780.00000000, 'Service Tax @12%', 427, 'ARDI6/000743/NCR' union all
select 12000.00000000, 'Mobile Charges', 427, 'ARDI6/000743/NCR' union all
select 495.60000000, 'Education Cess @ 2% on Service Tax', 427, 'ARDI6/000743/NCR' union all
select 24780.00000000, 'Service Tax @12%', 427, 'ARDI6/000743/NCR' union all
select 12000.00000000, 'Mobile Charges', 427, 'ARDI6/000743/NCR' union all
select 495.60000000, 'Education Cess @ 2% on Service Tax', 427, 'ARDI6/000743/NCR' union all
select 24780.00000000, 'Service Tax @12%', 427, 'ARDI6/000743/NCR' union all
select 495.60000000, 'Education Cess @ 2% on Service Tax', 427, 'ARDI6/000743/NCR' union all
select 12000.00000000, 'Mobile Charges', 427, 'ARDI6/000743/NCR' union all
select 495.60000000, 'Education Cess @ 2% on Service Tax', 427, 'ARDI6/000743/NCR' union all
select 24780.00000000, 'Service Tax @12%', 427, 'ARDI6/000743/NCR' union all
select 12000.00000000, 'Mobile Charges', 427, 'ARDI6/000743/NCR' union all
select 24780.00000000, 'Service Tax @12%', 427, 'ARDI6/000743/NCR' union all
select 71.13000000, 'Education Cess @ 2% on Service Tax', 427, 'ARDI6/000719/NCR' union all
select 3556.56000000, 'Service Tax @12%', 427, 'ARDI6/000719/NCR' union all
select 12000.00000000, 'Mobile Charges', 427, 'ARDI6/000743/NCR' union all
select 495.60000000, 'Education Cess @ 2% on Service Tax', 427, 'ARDI6/000743/NCR' union all
select 12000.00000000, 'Mobile Charges', 427, 'ARDI6/000743/NCR' union all
select 495.60000000, 'Education Cess @ 2% on Service Tax', 427, 'ARDI6/000743/NCR' union all
select 24780.00000000, 'Service Tax @12%', 427, 'ARDI6/000743/NCR' union all
select 24780.00000000, 'Service Tax @12%', 427, 'ARDI6/000743/NCR' union all
select 24780.00000000, 'Service Tax @12%', 427, 'ARDI6/000743/NCR'

-- do the work
select distinct x.water ColA,
case when x.pepsi <= 0 or x.pepsi < x.water or x.pepsi > x.cola then null else x.pepsi end ColB,
case when x.cola = x.water then null else x.cola end ColC,
t1.tcd_desc + case when t2.tcd_desc is null then '' else ' ' + t2.tcd_desc end + case when x.cola = x.water then '' else ' ' + t3.tcd_desc end + ' ' + x.inv_no Information
from (
select inv_no,
min(tcd_amt) water,
sum(tcd_amt) - min(tcd_amt) - max(tcd_amt) pepsi,
max(tcd_amt) cola
from (select distinct inv_no, host_id, tcd_desc, tcd_amt from @tcd_temp where host_id = 427) q
-- where inv_no = 'ARDI6/000743/NCR'
group by inv_no
) x
inner join @tcd_temp t1 on t1.inv_no = x.inv_no and t1.tcd_amt = x.water and t1.host_id = 427 --and t1.inv_no = 'ARDI6/000743/NCR'
left join @tcd_temp t2 on t2.inv_no = x.inv_no and t2.tcd_amt = x.pepsi and t2.host_id = 427 --and t2.inv_no = 'ARDI6/000743/NCR'
inner join @tcd_temp t3 on t3.inv_no = x.inv_no and t3.tcd_amt = x.cola and t3.host_id = 427 --and t3.inv_no = 'ARDI6/000743/NCR'[/code]
Peter Larsson
Helsingborg, Sweden

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-31 : 04:54:34

Thanks Peter Larsson
It's Working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources