Solved

Union and Group by

Posted on 2015-01-23
12
82 Views
Last Modified: 2015-01-23
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
Comment
Question by:VBdotnet2005
  • 6
  • 3
  • 3
12 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 40567114
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
 

Author Comment

by:VBdotnet2005
ID: 40567120
Yes, please
0
 

Author Comment

by:VBdotnet2005
ID: 40567121
I don't have account for your link.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 40567130
HI,

An account is free ...

Regards
  David
0
 

Author Comment

by:VBdotnet2005
ID: 40567152
Can you use my sample and modify it instead?
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 40567160
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:VBdotnet2005
ID: 40567177
Is "Stuff" a function?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 40567178
yes it adds or removes data within a string
0
 
LVL 35

Expert Comment

by:David Todd
ID: 40567186
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
 

Author Comment

by:VBdotnet2005
ID: 40567274
Lowfatspread,
I am curious. How can I use where inside left outer join (select distinct zip from ziptable)  ?
0
 

Author Comment

by:VBdotnet2005
ID: 40567280
never mind :)
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 40567281
what do you need to do ?

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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now