How to format field cells in SQL?

Question

1.00/5 (1 vote)

See more:

Hello every one.

I have an SQL question a little bit tricky.

I have two tables with headers and lines linked by a FK in the line table. For example:

SQL

Header
-------
PK_header
First_name
Last_name
...

and

SQL

Lines
-------
PK_lines
FK_header
Item_number
Quantity
Price

I wish to format like this

SQL

PK_header First_name Last_name Item_number Quantity Price
1         fn1        ln1       123         1        12.99
                               345         2        4.99
                               789         1        5.49
2         fn2        ln2       159         1        2.99
                               753         2        1.49
3         fn3        ln3       675         1        0.99
...

The problem I have is not retrieving the information but formating them the way you can see above.

So how can write this in SQL for SQL Server?

I hope you see what I wish for. Thanks in advance for your help.

Yours,

Fred.

As asked:
Here is the query to retrieve informations I made:

SQL

select
header.PK, header.First_name, header.Last_name, ...
lines.Item_number, lines.Quantity, lines.price
from header
inner join lines
on header.PK = lines.PK_header

This query displays each header for each lines. I wish to display the header data only when the PK changes.

It's for SQL Server 2000 and it has to be in a stored procedure.

Posted 26-Nov-14 21:42pm

bouli

Updated 27-Nov-14 0:29am

v4

Add a Solution

Comments

Tomas Takac 27-Nov-14 3:50am

Can you post the query you use to retrieve the information? (Use "improve question".)

bouli 27-Nov-14 4:02am

I have put the query you asked.
Best.

Tomas Takac 27-Nov-14 4:59am

Which version of SQL server?

bouli 27-Nov-14 6:27am

2000
It should be inside a stored procedure.

Tomas Takac 27-Nov-14 9:05am

Sorry mate, I would be able to do it in 2005 or later using row_number. But 2000 is ancient history! You really have strange requirements and outdated tools to implement them.

Sergey Alexandrovich Kryukov 27-Nov-14 3:52am

"Format" has nothing to do with SQL...
—SA

bouli 27-Nov-14 4:04am

It can be done in SQL or with Transact-SQL.

Tomas Takac 27-Nov-14 4:14am

I would agree with Sergey, you shouldn't try to format your data in the database. This is the task for the layer which displays your data.

bouli 27-Nov-14 4:16am

But I have to do it in SQL/Transact-SQL. I have no choice. :(
If I had the choice, I wouldn't ask it.

Sergey Alexandrovich Kryukov 27-Nov-14 4:20am

???

Maciej Los 28-Nov-14 8:53am

Agree!

4 solutions

Add a Solution

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

Mehdi Gholam · Answer 1 · 2014-11-26T21:57:00

Solution 1

SQL only gives you data in rows and columns, if you intend to "merge cells" that is up to your UI component/layer to do which is a different matter.

Posted 26-Nov-14 21:57pm

Mehdi Gholam

Comments

bouli 27-Nov-14 4:15am

Maybe it can be done with Transact-SQL?

Maciej Los 28-Nov-14 8:54am

Double 5!

Mehdi Gholam 28-Nov-14 8:58am

:)

bouli · Answer 2 · 2014-11-27T23:17:00

I have solved it by using a sub query like this:

SQL

select 
  case when (l.PK_lines = d.minL) then convert(varchar(50), e.PK_header) else '' end as PK_header,
l.Item_number,
...
from
(
select FK_header, min(PK_lines) minL
from Lines
group by FK_header
) as d
inner join Header h
on e.PK_header = d.FK_header
inner join Lines l
on l.FK_header = d.FK_header
order by d.minL

sort of...

Senthilnathan Vaithi · Answer 3 · 2014-11-28T02:30:00

Hi,

You get the exact format by Group by and Roll Up classes. below find my query. i have simply created this query manually. please try this in your DB and let me know.

SQL

SELECT CASE WHEN (Grouping(PK_header)=1) THEN 'MainTotal' ELSE PK_header END AS PK_header,
CASE WHEN (Grouping(First_name)=1) THEN 'SubTotal1' ELSE First_name END AS First_name,
CASE WHEN (Grouping(Last_name)=1) THEN 'SubTotal2' ELSE Last_name END AS Last_name,
CASE WHEN (Grouping(Item_number)=1) THEN 'SubTotal3' ELSE Item_number END AS Item_number,
CASE WHEN (Grouping(Quantity)=1) THEN 'SubTotal4' ELSE Quantity END AS Quantity,
Sum (case When Price>=0 then CASE_REF end ) as [Price] from Header,Lines
where  PK_header=Fk_header
 group by PK_header,First_name,Last_name,Item_number,Quantity WITH ROLLUP

For more help :

http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx[^]

yusuf_ahmed · Answer 4 · 2014-11-28T19:20:00

Try this,

SQL

select case(s.PK_lines) when sn.PK_lines then l.FK_header else null end FK_header
        ,case(s.PK_lines) when sn.PK_lines then l.First_name else null end First_name
        ,case(s.PK_lines) when sn.PK_lines then l.Last_name else null end Last_name
        ,s.Item_number,s.Quantity,s.Price
from Header s
    inner join Lines l on l.FK_header=s.FK_header
    inner join (select min(si.PK_lines) PK_lines,si.FK_header
                from Header si
                group by si.FK_header)sn on sn.FK_header=l.FK_header
order by l.FK_header

How to format field cells in SQL?

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

How to format field cells in SQL?

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us