MS SQL Query for Store Procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi All
I have a table like below

HTML

Rack    Cell    Qty
A1	1	50
A2	2	20
A3	3	70
B1	1	80
B2	3	90
C1	1	56
C3	3	77
C4	4	400
D1	5	55
D2	6	66

After executing the following Store Procedure

SQL

ALTER PROCEDURE [dbo].[SP_TestForAccess]
AS
BEGIN
DECLARE @query NVARCHAR(4000)
DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols +',['+ cell +']','['+ cell +']') FROM (SELECT DISTINCT cell FROM TestForAccess)as T
PRINT @cols
SET @query ='select rack,'+@cols+' FROM (select rack,cell,qty FROM TestForAccess)AS D pivot(sum(qty) for cell in ('+@cols+')) as P;'
--EXEC SP_EXECUTESQL @query
PRINT @query
EXECUTE(@query)
END

I am getting the following table
Here I am changing the cell value into columns

HTML

Rack    1       2       3       4       5       6   
A1	50	NULL	NULL	NULL	NULL	NULL
A2	NULL	20	NULL	NULL	NULL	NULL
A3	NULL	NULL	70	NULL	NULL	NULL
B1	80	NULL	NULL	NULL	NULL	NULL
B2	NULL	NULL	90	NULL	NULL	NULL
C1	56	NULL	NULL	NULL	NULL	NULL
C3	NULL	NULL	77	NULL	NULL	NULL
C4	NULL	NULL	NULL	400	NULL	NULL
D1	NULL	NULL	NULL	NULL	55	NULL
D2	NULL	NULL	NULL	NULL	NULL	66

Now my intention is to make group to the Rack column like below

HTML

     Rack    1       2       3       4       5       6
     A1    50  NULL    NULL    NULL    NULL    NULL
A    A2    NULL    20  NULL    NULL    NULL    NULL
     A3        NULL    NULL    70  NULL    NULL    NULL

     B1    80  NULL    NULL    NULL    NULL    NULL
B    B2    NULL    NULL    90  NULL    NULL    NULL

     C1    56  NULL    NULL    NULL    NULL    NULL
C    C3    NULL    NULL    77  NULL    NULL    NULL
     C4    NULL    NULL    NULL    400 NULL    NULL

     D1    NULL    NULL    NULL    NULL    55  NULL
D    D2    NULL    NULL    NULL    NULL    NULL    66

What should I change the above store procedure to get like the above table
Thanks to All

Posted 8-Aug-12 1:56am

IndrajitDasgupat

Updated 8-Aug-12 2:33am

AshishChaudha

v2

Add a Solution

Comments

[no name] 8-Aug-12 8:25am

Look up "group by"

1 solution

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)

Aarti Meswania · Accepted Answer · 2012-08-08T03:49:00

Solution 1

see example given below

SQL

select RackGrp as RackGrp, Rack as Rack from
(
select 'a1' as Rack,substring('a1',1,1) as RackGrp
union all
select 'a2' as Rack,substring('a2',1,1) as RackGrp
union all
select 'b1' as Rack,substring('b1',1,1) as RackGrp
) as a

Happy Coding!
:)

Posted 8-Aug-12 3:49am

Aarti Meswania

Updated 8-Aug-12 18:47pm

v2

Comments

Christian Amado 8-Aug-12 9:51am

Nice code!

Aarti Meswania 8-Aug-12 9:52am

thank you! :)

IndrajitDasgupat 9-Aug-12 0:40am

Hi
I am little new in SQL if give little more information about your code then it will help me lot to solve the issue
Thanks

Aarti Meswania 9-Aug-12 0:46am

you have column 'Rack' format is 'a1','a2'...,'b1'...
now you want group-'a','b'... and want to make 'Rack' column as subgroup.
so, using 'substring()' function of sql I have separated 'a' from 'a1',
Input='a1' -> apply function substring('a1',1,1) as b -> output ='a'
this way you will get first character as maingroup & rack-column will be subgroup of it.

IndrajitDasgupat 9-Aug-12 1:19am

Yes it is coming like below
Rackgrp Rack
a a1
a a2
b b1

But My requirement Like below
Rackgrp Rack
a1
a a2
a3

b1
b b2

Thanks for your helping attitude

Aarti Meswania 9-Aug-12 1:25am

yes, you will get this answer in sql, at client-side you can show it any format
eg. crosstab report make column 1. rackgrp, 2. rack, then it will view the output same as you want to see. view of information we manage on our forms else we directly use sql,sql isn't user-interface it's back-end. if sql can do so then there is no need to design forms. sql will only give data in fixed format (table-view) you should adjust display as you want in user-interface.
most welcome :)

IndrajitDasgupat 9-Aug-12 2:11am

Yes I tried same in C# windows form Data Grid View control but there is also same problem that is group making same table is not possible.

Thanks

Aarti Meswania 9-Aug-12 2:19am

this is another question post it as different Question,
'Datagridview grouping data / Nested view style'.
but, just for your information, It's not simple to view data as you desired in windows gridview
search on google - for topic 'cell-merging for diplay data in groups'. they will guide you how to handling paint event to show data in group.
In crystal-report (crosstab-view) you can easily achieve this.
It will also easy to achieve with third party controls e.g. Telerik Rad controls for winforms.

IndrajitDasgupat 10-Aug-12 0:48am

if you are .Net developer then I can send you a link where I got the same article
but small doubt is there
Please response

Aarti Meswania 10-Aug-12 2:58am

ok send link I will try my best to solve doubt

IndrajitDasgupat 10-Aug-12 3:27am

http://www.codeproject.com/Questions/152113/How-can-i-merge-DataGridView-Rows-Cells-with-Equal

if I fetch the data from SQL Server and creating Data Grid View in run time
then error in the following area error is coming
Error is : "Object reference not set to an instance of an object."
But Why Reference object here

private void Merge()
{
else
{
MergeCells(RowsToMerge[0], RowsToMerge[1], dataGrid.Columns["rack"].Index, isSelectedCell(RowsToMerge, dataGrid.Columns["rack"].Index) ? true : false);
CollectMergedRowsInFirstColumn(RowsToMerge[0], RowsToMerge[1]);
RowsToMerge[0] = -1;
}
}

Aarti Meswania 10-Aug-12 3:36am

put debugging you are getting error "Object reference not set to an instance of an object." It means some where you have access method or property of object which is already nothing
e.g

Dim dt as new datatable
dt=db_fetch_data("select * from table") 'suppose this function is return nothing because query was false
then dt is now 'nothing'
now, if I write
dt.rows.count -> this line will give exception "object reference ..."
because dt is an object but it's set to nothing and we try to access property here of actually nothing value

IndrajitDasgupat 10-Aug-12 4:46am

as soon as it is entering there it shows error even it is not going to merge function also i.e.
private void MergeCells(int RowId1, int RowId2, int Column, bool isSelected)
{
}
if you want I can give you enter code with my database backup

Aarti Meswania 10-Aug-12 5:32am

have you put condition
if (readyToMerge)
in grid's paint event
I think when grid have no data that time it's going to call merge
so, on load event when you fill grid with data then set value of 'readyToMerge'=True. then call grid.Invalidate() event -> it will fire paint event and that time 'readyToMerge'=True so will call cell-merging function.
I can solve it if you post project but, I think you should solve it your self. no problem to take guidance, because 1 Error cause to learn few more new things. If after trying this solution you face difficulty then ask me. :)

IndrajitDasgupat 10-Aug-12 5:55am

No I will SOLVE the issue.I could not catch the issue immediately because I don't have master that is my problem you just point it I will definitely fix the issue by my self.
Please keep your helping attitude ALIVE

Aarti Meswania 10-Aug-12 6:01am

sure I will, It's really good decision, I am sure you will learn many new topics from this error. check that code for 'readyToMerge' condition in paint event, I think there is no data in grid and trying to apply merge-effect. because paint event fires when page initialized and that time grid is empty.