Solved

Stored Proc help...

Posted on 2014-04-11
6
194 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
ID: 39994636
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
ID: 39994780
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
ID: 39994877
--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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 15

Expert Comment

by:jorge_toriz
ID: 39994945
Are you working on SQL Server 2012?
0
 
LVL 33

Author Closing Comment

by:Mike Eghtebas
ID: 40033295
Thank you.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40034541
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

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

920 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

18 Experts available now in Live!

Get 1:1 Help Now