Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

Union and Group by

I want to group them up. This is my sample data and the result I want it to returns.

cellprepared      ID      COL001      COL002      COL003      COL004      COL005      COL006      COL007      COL008
ZIP      1      2014-12-31 00:00:00      99            AAA                        
ZIP      4      2014-12-02 00:00:00             993710      AAA                        
ZIP      7      2014-12-10 00:00:00             993720      AAA                        
TRANSNO      1      2014-12-31 00:00:00      99            AAA                        
TRANSNO      2      2014-12-17 00:00:00      12303            AAA                        


Result

ID  COL001              COL002  COL003  COL004 COL005 COL006 COL007 COL008 cellprepared
1   2014-12-31 00:00:00 99      AAA                                        ZIP, TRANSNO
2   2014-12-17 00:00:00            AAA                                       TRANSNO
etc


SELECT 'ZIP' as cellprepared, * FROM      TMPTABLE
WHERE Col002 NOT IN (SELECT distinct ZIP FROM ZIPTABLE)
UNION ALL
SELECT 'TRANSNO' as cellprepared, *
FROM TMPTABLE
WHERE not exists (Select transno_no from TRANSNOTABLE where transno_no = Col003)
0
VBdotnet2005
Asked:
VBdotnet2005
  • 6
  • 3
  • 3
1 Solution
 
David ToddSenior DBACommented:
Hi,

What you are asking for is that cellprepared be concatenated.

I wrote a short piece on how to do this here:
http://www.sqlservercentral.com/scripts/Miscellaneous/31894/

HTH
  David
0
 
VBdotnet2005Author Commented:
Yes, please
0
 
VBdotnet2005Author Commented:
I don't have account for your link.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
David ToddSenior DBACommented:
HI,

An account is free ...

Regards
  David
0
 
VBdotnet2005Author Commented:
Can you use my sample and modify it instead?
0
 
LowfatspreadCommented:
doesn't this do what you desire?

select id,col001,col002,col003,col004,col005,col006,col007,col008
   ,Stuff(ltrim(case when z.zip is null then ',ZIP' else ' ' end
   +case when tn.transno is null then ',TRANSCODE' else ' ' end)
    ),1,0,'')   
 as cellprepared
from tmptable as t
left outer join (select distinct zip from ziptable) as z
on t.col002=z.zip
left outer join (select distinct transno_no from transnotable) as TN
on t.col003 = tn.transno_no
order by id

Open in new window


how large are the tables... ?
0
 
VBdotnet2005Author Commented:
Is "Stuff" a function?
0
 
LowfatspreadCommented:
yes it adds or removes data within a string
0
 
David ToddSenior DBACommented:
Hi,

Code from the article mentioned above (pubs was a test table for SQL 2000)
use pubs
go

if exists
	(
	select name 
	from sysobjects 
	where name = N'test' 
	and type = 'u'
	)
    drop table test
go

create table test (
	id			int,
	ord			char( 30 ),
	indexnum	int
	);

/*	
205             apples               125
205             shoes                223
305             pizza                 309
305             marbles             789
*/
	
set nocount on 

insert test values( 205, 'apples', 125 )
insert test values( 205, 'shoes', 223 )
insert test values( 305, 'pizza', 309 )
insert test values( 305, 'marbles', 789 )

set nocount off

-- Values in table
select t2.id, t2.ord, t2.indexnum
           from test t2
	
-- Grouping by id	
select t2.id, min( t2.ord )
from test t2
group by t2.id 
order by t2.id asc

-- Selecting 1 id
select t2.ord
from test t2
where t2.id = 205

/*
	This is the usual way to produce a comma delimited list,
	but it does not group it. It returns one string for the 
	entire results set
*/

declare @OrderList varchar(100)

select 
	@OrderList = coalesce( @OrderList + ', ', '') + rtrim( t2.ord )
from test t2
where t2.id = 205

select @OrderList

set @OrderList = null
go

-- Using a scaler function to return the comma delimited list
if 
	object_id (N'dbo.concatFieldNames') is not null
	drop function dbo.concatFieldNames
go

create function concatFieldNames (@TableID int)
returns varchar(8000)
as
begin
declare @fields varchar(8000)
set @fields = null
select @fields = coalesce( @fields + ', ', '' ) + rtrim( ord )
from test
where id = @tableid
return @fields
end --function
go

-- The select to use the function
select distinct id, dbo.concatFieldNames(ID)
from test

declare @total int

select
	@total = coalesce( @total, 0 ) + indexnum
from test

select @total

select sum( indexnum )
from test

Open in new window

0
 
VBdotnet2005Author Commented:
Lowfatspread,
I am curious. How can I use where inside left outer join (select distinct zip from ziptable)  ?
0
 
VBdotnet2005Author Commented:
never mind :)
0
 
LowfatspreadCommented:
what do you need to do ?

(select distinct zip from ziptable where ...... ) as z
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now