Using Cross tab updates in SQL2008

Question

Post reply

Using Cross tab updates in SQL2008

pverma

Grasshopper

Points: 20
More actions
March 4, 2011 at 6:40 am

#234758

We are using the below table to display data in a GridView:
-- Create a Demo Database
create database crosstab_demo
GO
use crosstab_demo
GO
-- Create a demo Table
create table demo1
(
field_id int IDENTITY(10000,1) CONSTRAINT [PK_field] PRIMARY KEY,
rownum int not null,
value varchar(50)
)
GO
-- Insert values into the table
insert into demo1 values(1,'A')
insert into demo1 values(1,'B')
insert into demo1 values(1,'C')
insert into demo1 values(1,'D')
insert into demo1 values(2,'AA')
insert into demo1 values(2,'BB')
insert into demo1 values(2,'CC')
insert into demo1 values(2,'DD')
GO
select * from demo1
GO
The above table displays the data as below:
field_id | rownum | value
10000 | 1 | A
10000 | 1 | B
10000 | 1 | C
10000 | 1 | D
10000 | 1 | AA
10000 | 1 | BB
10000 | 1 | CC
10000 | 1 | DD
But the desired view to be displayed in the GridView is:
1 A |B | C | D
2 AA|BB|CC|DD
We are supporting InPlace edit option in the GridView.
The problem is we wanted to have a mechanism to automatically update or insert data from the grid itself.
Could you please provide us with some technical assistance to achieve this with minimum effort involved?
Thanks…

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 1

Substitute a "pipe" for the comma's in the following article...

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pverma Grasshopper Points: 20 More actions · Answer 2

Jeff

Thanks for the prompt reply.

But it seems u have misunderstood my post.

The "pipe" used in the post is just for structuring the data in a table format.

Kindly execute the query posted to get the proper table structure.

We require the columns to be displayed as rows in the GridView and allow InPlace edit option to the user wherein user can insert/ update or Delete records from the GridView.

Regards...

LutzM SSC Guru Points: 107049 More actions · Answer 3

I would recommend to perform the pivot at the application layer including the steps required to get the changed values out of the GridView and update the data in the source table.

Otherwise you would have to deal with the unpivot part as well as detecting the changed values using T-SQL. Sure, it's possible. But I, personally, wouldn't do it that way.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 4

pverma (3/4/2011)
Jeff
Thanks for the prompt reply.
But it seems u have misunderstood my post.
The "pipe" used in the post is just for structuring the data in a table format.
Kindly execute the query posted to get the proper table structure.
We require the columns to be displayed as rows in the GridView and allow InPlace edit option to the user wherein user can insert/ update or Delete records from the GridView.
Regards...

So what do you actually want for output if you don't want what you posted which is...

1 A |B | C | D

2 AA|BB|CC|DD

Notice the pipes? If you don't want those in the output, please show exactly how you want the output. Thanks.

You also mention "editing". Does that include the addition or deletion of any of those values?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pverma Grasshopper Points: 20 More actions · Answer 5

We are sorry for the confusion created.

Below is the html code which you can save as a HTML file and view it in your Internet Browser which demonstrates how exactly the grid should look like which allows Add/Update/Delete as InPlace edit mode.

----------------------------------------

<html>

<body>

<td>Delete</td>

</tr>

<td>Delete</td>

</tr>

</table>

</body>

</html>

---------------------------------

Kindly refer to the previously posted script for the Database.