Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
here is query
 
SELECT   c.Customer_CNIC,g.Guarantor_CNIC FROM Customer c,Guaronters g where g.Customer_CNIC=c.Customer_CNIC and c.Customer_CNIC='12345-1234567-8' order by c.Customer_CNIC
 

 
i m getting result as:
Customer_CNIC         Guarantor_CNIC
12345-1234567-8       78663-2349875-6
12345-1234567-8       87676-5765757-6
 

 
but i want as:
Customer_CNIC         Guarantor_CNIC
12345-1234567-8       78663-2349875-6
                      87676-5765757-6
Posted 10-Jan-13 3:38am
Comments
joshrduncan2012 at 10-Jan-13 9:44am
   
group by clause?
zeshanazam at 10-Jan-13 10:00am
   
??
PIEBALDconsult at 10-Jan-13 9:53am
   
That should probably be handled in the presentation layer; not the data layer.
zeshanazam at 10-Jan-13 10:00am
   
how ?
PIEBALDconsult at 10-Jan-13 10:08am
   
That would depend on the presentation layer. What are you using? Is it a Web site? Crystal Reports or similar?
 
Look into level breaks.
zeshanazam at 10-Jan-13 10:09am
   
using sql
PIEBALDconsult at 10-Jan-13 10:13am
   
That is not a system for presentation; that is a language for querying and manipulating data.
zeshanazam at 10-Jan-13 10:17am
   
what changes have to be made in query
PIEBALDconsult at 10-Jan-13 13:39pm
   
None.
zeshanazam at 10-Jan-13 13:44pm
   
then how can i get require results ?
Maciej Los at 10-Jan-13 15:57pm
   
Agree!!!
My virtual 5!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

There is no mechanism I know of in SQL to do that, and for good reason.
SQL is not about presentation of the data, each row is intended to be self contained, and not to rely on earlier results (because the order of processing of records is not guaranteed by SQL Server).
 
To do that, you would have to create a Stored procedure that selected the rows into a temporary table and looped through them creating a second temporary table for output. Nasty, and inefficient.
 
This is a task with is much, much more easily handled in the presentation layer, whatever that happens to be coded in.
  Permalink  
Comments
Maciej Los at 10-Jan-13 16:30pm
   
+5!
Mika Wendelius at 12-Jan-13 2:53am
   
Fully agree.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

As i wrote before: SQL is not for 'visualize' data!
PIEBALDconsult wrote:
That is not a system for presentation; that is a language for querying and manipulating data.

OriginalGriff wrote:
SQL is not about presentation of the data, each row is intended to be self contained (...)

How many times we need to repeat it?
 
BTW:
You need to read about JOIN[^] statement. By using joins[^], you can retrieve data from two or more tables based on logical relationships between the tables. For example:
SELECT   c.Customer_CNIC, g.Guarantor_CNIC
FROM Customer c INNER JOIN Guaronters g ON g.Customer_CNIC=c.Customer_CNIC 
WHERE c.Customer_CNIC='12345-1234567-8'
ORDER BY c.Customer_CNIC
 
Tips (to achieve that what OriginalGriff wrote):
1) How to CREATE TABLE[^]? - section Temporary tables. There you'll find informations about: how to insert data into temporary table.
2) How to go through the collection of records? Use cursor[^] and WHILE[^] command - section: B. Using WHILE in a cursor.
  Permalink  
Comments
Mika Wendelius at 12-Jan-13 2:53am
   
Using a temp table is a good option, 5.
Maciej Los at 12-Jan-13 16:24pm
   
Thank you, Mika.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

As said by others, formatting shouldn't be done using SQL. But if you still for some reason want to do it, depending on the database, you could use LAG.
 
For example, have a look at: How to fetch data from the previous or next rows in the resultset[^]
  Permalink  
Comments
Maciej Los at 11-Jan-13 17:26pm
   
Good answer, perfect tip! +5!
Mika Wendelius at 12-Jan-13 2:54am
   
Thanks :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 248
1 Maciej Los 210
2 Sergey Alexandrovich Kryukov 160
3 Peter Leow 155
4 George Jonsson 135


Advertise | Privacy | Mobile
Web01 | 2.8.140709.1 | Last Updated 11 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid