Display rows as columns

Hi
i have a table like
Id , ColumnName group
1 C1 1
2 C2 1
3 C3 2
these columns are mapped to another table like
Id C1 C2 C3....
so the columns tht must be selected from the transaction table differs
according to the group in the mapping table.
so can i write a query to get the data from transaction table for a group
in mapping table.
I DONT WANT TO USE DYNAMICALLY CREATED QUERY..
plz send the solution as the client is waiting
RenjithHi Renjith
Probably you can use CASE statement for this. This is similar to DECODE
statement available in Oracle.
Please post the expected result so that we can give u a solution
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Renjith" wrote:

> Hi
> i have a table like
> Id , ColumnName group
> 1 C1 1
> 2 C2 1
> 3 C3 2
> these columns are mapped to another table like
> Id C1 C2 C3....
> so the columns tht must be selected from the transaction table differs
> according to the group in the mapping table.
> so can i write a query to get the data from transaction table for a group
> in mapping table.
> I DONT WANT TO USE DYNAMICALLY CREATED QUERY..
> plz send the solution as the client is waiting
> Renjith
>|||> Id , ColumnName group
> 1 C1 1
> 2 C2 1
> 3 C3 2
> these columns are mapped to another table like
> Id C1 C2 C3....
Sounds like a cross-tab / pivot.
http://www.aspfaq.com/2462

> plz send the solution as the client is waiting
So why is the client paying YOU? I wonder what the client would think if
they saw your post and recognized you as their consultant?|||Hi
i need the result as
when i select group = 1
Id C1 C2
the value of c1 , c2 will be transaction table
when i select group = 2
Id C3
the value of C3 will be transaction table
Renjith
"Renjith" wrote:

> Hi
> i have a table like
> Id , ColumnName group
> 1 C1 1
> 2 C2 1
> 3 C3 2
> these columns are mapped to another table like
> Id C1 C2 C3....
> so the columns tht must be selected from the transaction table differs
> according to the group in the mapping table.
> so can i write a query to get the data from transaction table for a group
> in mapping table.
> I DONT WANT TO USE DYNAMICALLY CREATED QUERY..
> plz send the solution as the client is waiting
> Renjith
>|||> i need the result as
> when i select group = 1
> Id C1 C2
> the value of c1 , c2 will be transaction table
> when i select group = 2
> Id C3
> the value of C3 will be transaction table
http://www.aspfaq.com/5006

Display group header with no items

I have to display group header inspite of not having records in that group.
Group1
values
group2
No valuesI did this, using a stored procedure, by inserting a row into the dataset
for each column heading you require with the name of the column heading as
the value for the "column heading" field in the dataset:
INSERT INTO tablename([column heading], [row heading], [detail])
VALUES("column heading", NULL, NULL)
If you want it to sort in a specific way, i.e. not alphabetical, add another
field (called "sort" perhaps) and then set the value of the sort field in
each row to the numbered location in the column groups you want it to appear
in. You then sort by the "sort" field. Its a bit messy, but it gets the
job done.
I am guessing somehow has a better idea, but if not then try this cos it
works.
Jarryd
"NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
news:F0FFA70C-AA27-44AE-9B83-BE0A62B6343E@.microsoft.com...
>I have to display group header inspite of not having records in that group.
> Group1
> values
> group2
> No values
>|||Is this an good idea if i have 4 different table results unioned by union.
Then the record in the first table might not match the same group record in
second table then. I am missing inspite of it.
"Jarryd" wrote:
> I did this, using a stored procedure, by inserting a row into the dataset
> for each column heading you require with the name of the column heading as
> the value for the "column heading" field in the dataset:
> INSERT INTO tablename([column heading], [row heading], [detail])
> VALUES("column heading", NULL, NULL)
> If you want it to sort in a specific way, i.e. not alphabetical, add another
> field (called "sort" perhaps) and then set the value of the sort field in
> each row to the numbered location in the column groups you want it to appear
> in. You then sort by the "sort" field. Its a bit messy, but it gets the
> job done.
> I am guessing somehow has a better idea, but if not then try this cos it
> works.
> Jarryd
> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
> news:F0FFA70C-AA27-44AE-9B83-BE0A62B6343E@.microsoft.com...
> >I have to display group header inspite of not having records in that group.
> >
> > Group1
> >
> > values
> >
> > group2
> >
> > No values
> >
>
>|||Why not dump the final result set to a virtaul table, and then insert the
columns as I explained into the virtual table. Then select all from the
virtual table and base the report on that dataset.
HTH.
Jarryd
"NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
news:48C1A492-50DA-41DA-AA1A-C3D046988600@.microsoft.com...
> Is this an good idea if i have 4 different table results unioned by union.
> Then the record in the first table might not match the same group record
> in
> second table then. I am missing inspite of it.
>
> "Jarryd" wrote:
>> I did this, using a stored procedure, by inserting a row into the dataset
>> for each column heading you require with the name of the column heading
>> as
>> the value for the "column heading" field in the dataset:
>> INSERT INTO tablename([column heading], [row heading], [detail])
>> VALUES("column heading", NULL, NULL)
>> If you want it to sort in a specific way, i.e. not alphabetical, add
>> another
>> field (called "sort" perhaps) and then set the value of the sort field in
>> each row to the numbered location in the column groups you want it to
>> appear
>> in. You then sort by the "sort" field. Its a bit messy, but it gets the
>> job done.
>> I am guessing somehow has a better idea, but if not then try this cos it
>> works.
>> Jarryd
>> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
>> news:F0FFA70C-AA27-44AE-9B83-BE0A62B6343E@.microsoft.com...
>> >I have to display group header inspite of not having records in that
>> >group.
>> >
>> > Group1
>> >
>> > values
>> >
>> > group2
>> >
>> > No values
>> >
>>|||It not giving results as expected.
"Jarryd" wrote:
> Why not dump the final result set to a virtaul table, and then insert the
> columns as I explained into the virtual table. Then select all from the
> virtual table and base the report on that dataset.
> HTH.
> Jarryd
> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
> news:48C1A492-50DA-41DA-AA1A-C3D046988600@.microsoft.com...
> > Is this an good idea if i have 4 different table results unioned by union.
> > Then the record in the first table might not match the same group record
> > in
> > second table then. I am missing inspite of it.
> >
> >
> >
> > "Jarryd" wrote:
> >
> >> I did this, using a stored procedure, by inserting a row into the dataset
> >> for each column heading you require with the name of the column heading
> >> as
> >> the value for the "column heading" field in the dataset:
> >>
> >> INSERT INTO tablename([column heading], [row heading], [detail])
> >> VALUES("column heading", NULL, NULL)
> >>
> >> If you want it to sort in a specific way, i.e. not alphabetical, add
> >> another
> >> field (called "sort" perhaps) and then set the value of the sort field in
> >> each row to the numbered location in the column groups you want it to
> >> appear
> >> in. You then sort by the "sort" field. Its a bit messy, but it gets the
> >> job done.
> >>
> >> I am guessing somehow has a better idea, but if not then try this cos it
> >> works.
> >>
> >> Jarryd
> >>
> >> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
> >> news:F0FFA70C-AA27-44AE-9B83-BE0A62B6343E@.microsoft.com...
> >> >I have to display group header inspite of not having records in that
> >> >group.
> >> >
> >> > Group1
> >> >
> >> > values
> >> >
> >> > group2
> >> >
> >> > No values
> >> >
> >>
> >>
> >>
>
>|||What does the virtual table look like? I am thinking something simple,
like:
Virt table fields = [Column_heading], [Row_heading], [Detail]
With data:
Column_heading | Row_Heading | Detail
----
CH1 | RH1 | $100
CH2 | RH1 | $125
CH3 | RH1 | $142
CH1 | RH2 | $95
CH2 | RH2 | $117
CH3 | RH2 | $102
CH1 | RH4 | $95
CH2 | RH4 | $117
CH3 | RH4 | $102
It doesn't really matter if it is more complicated than that cos the
principal should be the same. If you want to add a column heading that
inspite of there not being an entry for one in the final dataset simply add
one with NULL detail and NULL Row_Heading, or if you want to add a
Row_Heading do the same sort of thing:
INSERT INTO #Virt_Table*([Column_Heading], [Row_Heading], [Detail])
VALUES('CH4', NULL, NULL)
INSERT INTO #Virt_Table*([Column_Heading], [Row_Heading], [Detail])
VALUES(NULL, 'RH3', NULL)
You should then end up with a matrix (if that is what you are making) that
looks like this:
CH1 | CH2 | CH3 | CH4
RH1| $100 | $125 | $142 | NULL
RH2| $95 | $117 | $102 | NULL
RH3| NULL | NULL | NULL | NULL
RH4| $95 | $117 | $102 | NULL
Extend that method to however complicated your dataset or groups of columns
are. I have said column heading here, but you can interchange that with
Group_heading to make it clearer.
HTH,
Jarryd
"NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
news:41625D86-F099-44F6-A2BB-55F341CCD2F6@.microsoft.com...
> It not giving results as expected.
> "Jarryd" wrote:
>> Why not dump the final result set to a virtaul table, and then insert the
>> columns as I explained into the virtual table. Then select all from the
>> virtual table and base the report on that dataset.
>> HTH.
>> Jarryd
>> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
>> news:48C1A492-50DA-41DA-AA1A-C3D046988600@.microsoft.com...
>> > Is this an good idea if i have 4 different table results unioned by
>> > union.
>> > Then the record in the first table might not match the same group
>> > record
>> > in
>> > second table then. I am missing inspite of it.
>> >
>> >
>> >
>> > "Jarryd" wrote:
>> >
>> >> I did this, using a stored procedure, by inserting a row into the
>> >> dataset
>> >> for each column heading you require with the name of the column
>> >> heading
>> >> as
>> >> the value for the "column heading" field in the dataset:
>> >>
>> >> INSERT INTO tablename([column heading], [row heading], [detail])
>> >> VALUES("column heading", NULL, NULL)
>> >>
>> >> If you want it to sort in a specific way, i.e. not alphabetical, add
>> >> another
>> >> field (called "sort" perhaps) and then set the value of the sort field
>> >> in
>> >> each row to the numbered location in the column groups you want it to
>> >> appear
>> >> in. You then sort by the "sort" field. Its a bit messy, but it gets
>> >> the
>> >> job done.
>> >>
>> >> I am guessing somehow has a better idea, but if not then try this cos
>> >> it
>> >> works.
>> >>
>> >> Jarryd
>> >>
>> >> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
>> >> news:F0FFA70C-AA27-44AE-9B83-BE0A62B6343E@.microsoft.com...
>> >> >I have to display group header inspite of not having records in that
>> >> >group.
>> >> >
>> >> > Group1
>> >> >
>> >> > values
>> >> >
>> >> > group2
>> >> >
>> >> > No values
>> >> >
>> >>
>> >>
>> >>
>>