Re: display data to user question

Note I can send this in a zip file if you supply me with an email. most of
the info like table schema and images did not made it. Sorry

I created a project that has an embedded SQl table with the scenario you
described

Database: DavesDataase.mdf
Tables: 1 - SalesSumm
2 - Yeartable
3 - Qttable
4 - Nametable

SaleSumm
col name
data type



year
nchar(4)



salesname
nvarchar(50)



Q1
SMALLINT



Q2
SMALLINT




Then other 3 table are design to denormalize the first


Yeartable
Column Name
Data type

col1
nvarchar(250)
year 1
col2
nvarchar(250)
year 2
col3
nvarchar(250)

col4
nvarchar(250)

col5
nvarchar(250)

col6
nvarchar(250)

col7
nvarchar(250)

seq
smallint



Qttable

Column Name
Data type

col1
nvarchar(250)

col2
nvarchar(250)

col3
nvarchar(250)
1q1
col4
nvarchar(250)
1q2
col5
nvarchar(250)
2q1
col6
nvarchar(250)
2q2
col7
nvarchar(250)

seq
smallint





Nametable
Column Name
Data type

col1
nvarchar(250)

col2
nvarchar(250)

col3
nvarchar(250)

col4
nvarchar(250)

col5
nvarchar(250)

col6
nvarchar(250)

col7
nvarchar(250)
name
seq
smallint


================================================================================================
Class module: DavesDataAccess.vb

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Public Class DavesDataAccess
Public Function AReallyBigSql() As Data.DataTable
TruncateTables()
Dim sql As String = _
"declare @YearOne nvarchar(4) " & _
"declare @YearTwo nvarchar(4) " & _
"declare @SalesName nvarchar(50) " & _
"declare @1Q1 nvarchar(4) " & _
"declare @1Q2 nvarchar(4) " & _
"declare @2Q1 nvarchar(4) " & _
"declare @2Q2 nvarchar(4) " & _
" " & _
"select top 1 @YearOne = cast([year] as nvarchar) from SalesSumm
" & _
"select top 1 @YearTwo = cast ([year] as nvarchar) from
SalesSumm where year <> @YearOne " & _
"select top 1 @SalesName = salesName from salesSumm " & _
"select top 1 @1Q1 = cast (q1 as nvarchar(2)) from salessumm " & _
"select top 1 @1Q2 = cast(q2 as nvarchar(2)) from salessumm " & _
"select top 1 @2Q1 = cast(q1 as nvarchar(2)) from salessumm
where year <> @YearOne " & _
"select top 1 @2Q2 = cast(q2 as nvarchar(2)) from salessumm
where year <> @YearOne " & _
" " & _
"insert into YearTable (seq, col1, col2) values(1, @YearOne,
@YearTwo) " & _
"insert into QtTable (seq,col3,col4,col5,col6) values(2, @1q1,
@1q2,@2q1, @2q2) " & _
"insert into NameTable (seq, col7) values(3, @salesName) " & _
" " & _
"select * from YearTable " & _
"union " & _
"select * from qttable " & _
"union " & _
"select * from NameTable" & _
" order by seq"


Dim cn As New SqlConnection("Data
Source=.\SQLEXPRESS;AttachDbFilename=C:\DevCenter\WebSites\DisplayOnItsSide\App_Data\DavesDatabase.mdf;Integrated Security=True;User Instance=True")
cn.Open()
Dim da As New SqlDataAdapter(sql, cn)
Dim dt As New Data.DataTable
da.Fill(dt)
cn.Close()
Return dt
End Function

Public Sub TruncateTables()
Dim sql As String = _
"delete from nameTable"
Dim cn As New SqlConnection("Data
Source=.\SQLEXPRESS;AttachDbFilename=C:\DevCenter\WebSites\DisplayOnItsSide\App_Data\DavesDatabase.mdf;Integrated Security=True;User Instance=True")
cn.Open()
Dim cmd As New SqlCommand(sql, cn)

cmd.ExecuteNonQuery()
sql = "delete from YearTable"
cmd.CommandText = sql
cmd.ExecuteNonQuery()

sql = "delete from qttable"
cmd.CommandText = sql
cmd.ExecuteNonQuery()

cn.Close()
End Sub

End Class

================================================================================================
Page - Default.aspx

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb"
Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>

<html xmlns="http://www.w3.org/1999/xhtml";>
<head runat="server">
<title>Untitled Page</title>
<style type="text/css">
.style1
{
width: 100%;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataList ID="DataList1" runat="server" Height="495px"
Width="288px">
<ItemTemplate>

<table class="style1">
<tr>
<td align="center" colspan="2">
<asp:Label ID="lblYearOne" runat="server"
Text='<%# Eval("col1") %>'></asp:Label>
</td>
<td align="center" colspan="2">
<asp:Label ID="lblYearTwo" runat="server"
Text='<%# Eval("col2") %>'></asp:Label>
</td>
</tr>
<tr>
<td align="center">
<asp:Label ID="lbl1Q1" runat="server" Text='<%#
Eval("col3") %>'></asp:Label>
</td>
<td align="center">
<asp:Label ID="lbl1Q2" runat="server" Text='<%#
Eval("col4") %>'></asp:Label>
</td>
<td align="center">
<asp:Label ID="lbl2Q1" runat="server" Text='<%#
Eval("col5") %>'></asp:Label>
</td>
<td align="center">
<asp:Label ID="lbl2Q2" runat="server" Text='<%#
Eval("col6") %>'></asp:Label>
</td>
</tr>
<tr>
<td colspan="4">
<asp:Label ID="lblName" runat="server" Text='<%#
Eval("col7") %>'></asp:Label>
</td>
</tr>
<tr>
<td>
</td>
<td>
</td>
<td>
</td>
<td>
</td>
</tr>
</table>
</ItemTemplate>
</asp:DataList>
</div>
</form>
</body>
</html>

Code behind

Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
BindDataList()
End Sub

Private Sub BindDataList()
Dim da As New DavesDataAccess

Dim dt As Data.DataTable = da.AReallyBigSql

DataList1.DataSource = dt
DataList1.DataBind()


End Sub
End Class

===========================================================================================

A second example
Page: Repeater.aspx

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Repeater.aspx.vb"
Inherits="Repeater" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>

<html xmlns="http://www.w3.org/1999/xhtml";>
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate>
<asp:Label ID="lblYrOne" runat="server" Text='<%#
Eval("col1") %>'></asp:Label>
<asp:Label ID="lblYrTwo" runat="server" Text='<%#
Eval("col2") %>'></asp:Label>
<br />
<asp:Label ID="lbl1Q1" runat="server" Text='<%# Eval("col3")
%>'></asp:Label>
<asp:Label ID="lbl1Q2" runat="server" Text='<%# Eval("col4")
%>'></asp:Label>
<asp:Label ID="lbl2Q1" runat="server" Text='<%# Eval("col5")
%>'></asp:Label>
<asp:Label ID="lbl2Q2" runat="server" Text='<%# Eval("col6")
%>'></asp:Label>
<br />
<asp:Label ID="lblName" runat="server" Text='<%#
Eval("col7") %>'></asp:Label>

</ItemTemplate>
</asp:Repeater>
</div>
</form>
</body>
</html>

Code behind


Partial Class Repeater
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
BindDataList()

End Sub


Private Sub BindDataList()
Dim da As New DavesDataAccess

Dim dt As Data.DataTable = da.AReallyBigSql

Repeater1.DataSource = dt
Repeater1.DataBind()


End Sub

End Class


So what it does if you look at dataaccess module

"declare @YearOne nvarchar(4) " & _
"declare @YearTwo nvarchar(4) " & _
"declare @SalesName nvarchar(50) " & _
"declare @1Q1 nvarchar(4) " & _
"declare @1Q2 nvarchar(4) " & _
"declare @2Q1 nvarchar(4) " & _
"declare @2Q2 nvarchar(4) " & _
" " & _
"select top 1 @YearOne = cast([year] as nvarchar) from SalesSumm
" & _
"select top 1 @YearTwo = cast ([year] as nvarchar) from
SalesSumm where year <> @YearOne " & _
"select top 1 @SalesName = salesName from salesSumm " & _
"select top 1 @1Q1 = cast (q1 as nvarchar(2)) from salessumm " & _
"select top 1 @1Q2 = cast(q2 as nvarchar(2)) from salessumm " & _
"select top 1 @2Q1 = cast(q1 as nvarchar(2)) from salessumm
where year <> @YearOne " & _
"select top 1 @2Q2 = cast(q2 as nvarchar(2)) from salessumm
where year <> @YearOne " & _
" " & _
"insert into YearTable (seq, col1, col2) values(1, @YearOne,
@YearTwo) " & _
"insert into QtTable (seq,col3,col4,col5,col6) values(2, @1q1,
@1q2,@2q1, @2q2) " & _
"insert into NameTable (seq, col7) values(3, @salesName) " & _
" " & _
"select * from YearTable " & _
"union " & _
"select * from qttable " & _
"union " & _
"select * from NameTable" & _
" order by seq"

is create a temp output using a union to join three sql queries making sure
the rows are in the right order using the order by seq.

The rest of the magic is in the databinding which you can look at in the
aspx stuff.

I did test this and it does give




Untitled Pagehttp://localhost:2248/DisplayOnItsSide/Repeater.aspx
Screen clipping taken: 2/17/2008, 12:19 PM


Hope this help. If you want the entire project I can zip it and email to a
point of preference.


--
aaa


"Dave" wrote:

Yeah that's the point I'm at. I have my data pivoted in SQL, I created a
datatable in code, but getting it to show on the GUI side by side isn't
happening very easy or at all.



"Angel" <rodoopus@xxxxxxxxxxx> wrote in message
news:620D03D3-07AE-4602-B99A-908E07C8A5D5@xxxxxxxxxxxxxxxx
That's interesting because I have something here that I started whre I
used
sql to do it. I stop because I figured we had the answer but I will look
it
up a gaind and send it to you. The problem is that you want to show the
years followed by name and the Quaters side by side in the appropriate
slot
with the years. The Grid will not do this easily. We can pump code to it
manipulate the datatable to provide us the right format but it will not be
simple.

Think if we can do it in t-sql we can do it in code but no matter how you
slice it it will require a significant amount of coding it will not be
trivial.
--
aaa


"Dave" wrote:

Angel,
Using the sql pivot still won't allow me to show my data side by
side. this thing is driving me nuts

"Angel" <rodoopus@xxxxxxxxxxx> wrote in message
news:DAF41379-5B4A-4FF4-BFE8-3E64D5D7E16E@xxxxxxxxxxxxxxxx
Dave,

I just realized what you were trying to do. What you want to do is to
turn
the table on its side basically to display rows as columns. There is
no
simple way of forcing the controls to do this. You can shape the data
with a
SQL query that actually perform the rotation.

I am working on the example anyway but it would not be as trivial as I
first
thought because I misunderstood the problem. So I am not going to be
ready
today with this solution as I promise so in the mean-time you can help
you
self by reviewing some the info below.

Definition

A pivot table is a data summarization tool found in data visualization
programs such as spreadsheets (e.g. Excel sheet). Among other
functions,
it
can automatically sort, count, and total the data stored in one table
or
spread*** and create a second table displaying the summarized data.
Pivot
tables are useful to create crosstabs quickly. The user sets up and
changes
the summary's structure by dragging and dropping fields graphically.
This
"rotation" or pivoting of the summary table gives the concept its name

Checkout these links

http://technet.microsoft.com/en-us/library/ms177410.aspx

http://jdixon.dotnetdevelopersjournal.com/pivot_table_data_in_sql_server_2000_and_2005.htm

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

http://www.databasejournal.com/features/mssql/article.php/3516331

Once I have a sample I will posted (if you still need it)

Hope this helps
--
aaa


"Dave" wrote:

I'm stuck on something and I've been trying to find some examples on
it.
I'm
trying to show the data to my users as such:

year1 year2
Q1 Q2 Q1 Q2
Smith

my dataset looks like this:
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<Table>
<year>2006</year>
<SalesName>Smith</SalesName>
<Q1>25</Q1>
<Q2>50</Q2>

</Table>
<Table>
<year>2005</year>
<SalesName>Smith</SalesName>
<Q1>25</Q1>
<Q2>50</Q2>
<Table>

is there any possible way with either the gridview, repeater,
datalist,
anything, that I can use to to show my data side by side? Any examples
would
help as well and be greatly apprecaited.












.