what changes have to be made in query. help me with this. thanx in advance

Question

1.00/5 (1 vote)

See more:

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

zeshanazam

Add a Solution

Comments

joshrduncan2012 10-Jan-13 9:44am

group by clause?

zeshanazam 10-Jan-13 10:00am

??

PIEBALDconsult 10-Jan-13 9:53am

That should probably be handled in the presentation layer; not the data layer.

zeshanazam 10-Jan-13 10:00am

how ?

PIEBALDconsult 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 10-Jan-13 10:09am

using sql

PIEBALDconsult 10-Jan-13 10:13am

That is not a system for presentation; that is a language for querying and manipulating data.

zeshanazam 10-Jan-13 10:17am

what changes have to be made in query

PIEBALDconsult 10-Jan-13 13:39pm

None.

zeshanazam 10-Jan-13 13:44pm

then how can i get require results ?

Maciej Los 10-Jan-13 15:57pm

Agree!!!
My virtual 5!

3 solutions

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:

SQL

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.

Posted 10-Jan-13 10:29am

Maciej Los

Comments

Wendelius 12-Jan-13 2:53am

Using a temp table is a good option, 5.

Maciej Los 12-Jan-13 16:24pm

Thank you, Mika.

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[^]

Posted 11-Jan-13 7:25am

Wendelius

Comments

Maciej Los 11-Jan-13 17:26pm

Good answer, perfect tip! +5!

Wendelius 12-Jan-13 2:54am

Thanks :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2013-01-10T05:11:00

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.

what changes have to be made in query. help me with this. thanx in advance

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us