Solved

Stored Proc help...

Posted on 2014-04-11
6
189 Views
Last Modified: 2014-05-01
In table Source below, there is a flag field with string values, c or e. Could you please help me with a stored proc to read table Source and put it table Target?

The c-value(e-value) from columns F1 and F2 will show up in the Source table while Name A, B, C each will show only once.
Source:
Name	   F1	   F2	flag
A	7	   3	   c
B	13	   4	   c
C	0	   6	   c
A	11	   8	   e
B	19	   9	   e
C	4	   0	   e
			
Target
Name	  F1	        F2	
A	7(11)	       3(8)	
B	13(19)	       4(9)	
C	-(4)	       6(-)

Open in new window


Thank you
0
Comment
Question by:Mike Eghtebas
6 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 200 total points
Comment Utility
You didn't provide any data type information so you may need to cast some values to make the query below work.

INSERT INTO Target (Name, F1, F2)
SELECT c.Name,
   CASE c.F1 WHEN '0' THEN '-' ELSE c.F1 END + '(' + CASE e.F1 WHEN '0' THEN '-' ELSE e.F1 END + ')',
   CASE c.F2 WHEN '0' THEN '-' ELSE c.F2 END + '(' + CASE e.F2 WHEN '0' THEN '-' ELSE e.F2 END + ')',
FROM Source AS c
INNER JOIN Source AS e
   ON c.Name = e.Name
   AND C.flag = 'c'
   AND e.flag = 'e'
0
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 150 total points
Comment Utility
INSERT	Target
(	Name
,	F1
,	F2
)
SELECT	c.name
,	F1	= isnull(nullif(cast(c.F1 as varchar),0),'-')
		+ '('
		+ isnull(nullif(cast(e.F1 as varchar),0),'-')
		+ ')'
,	F2	= isnull(nullif(cast(c.F2 as varchar),0),'-')
		+ '('
		+ isnull(nullif(cast(e.F2 as varchar),0),'-')
		+ ')'
FROM	Source	c 
JOIN	Source	e 	ON	c.name = e.name
			AND	c.flag = 'c'
			AND	e.flag = 'e'

Open in new window

0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 150 total points
Comment Utility
--INSERT INTO dbo.target ( Name, F1, F2 )
SELECT
    Name,
    ISNULL(NULLIF(CAST(MAX(CASE WHEN flag = 'c' THEN F1 END) AS varchar(10)), 0), '-') + '(' +
    ISNULL(NULLIF(CAST(MAX(CASE WHEN flag = 'e' THEN F1 END) AS varchar(10)), 0), '-') + ')' AS F1,
    ISNULL(NULLIF(CAST(MAX(CASE WHEN flag = 'c' THEN F2 END) AS varchar(10)), 0), '-') + '(' +
    ISNULL(NULLIF(CAST(MAX(CASE WHEN flag = 'e' THEN F2 END) AS varchar(10)), 0), '-') + ')' AS F2
FROM source
GROUP BY Name
ORDER BY Name


Or:
SELECT ...
INTO dbo.target
...
If you want to create a brand new target table from scratch.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 15

Expert Comment

by:jorge_toriz
Comment Utility
Are you working on SQL Server 2012?
0
 
LVL 33

Author Closing Comment

by:Mike Eghtebas
Comment Utility
Thank you.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Huh?  The accepted solution does not and cannot produce the output as stated in the initial request, because it will never combine/merge values from different rows.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

763 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

6 Experts available now in Live!

Get 1:1 Help Now