?
Solved

Union and Group by

Posted on 2015-01-23
12
Medium Priority
?
96 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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 2000 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
 

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

719 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