Solved

Union and Group by

Posted on 2015-01-23
12
93 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 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