Solved

Union and Group by

Posted on 2015-01-23
12
94 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

738 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