Variable Colums In Stacked Table

Dec 14, 2007

Hello, and thanks for taking the time to read this.

NOOB question:

In dealing with, say, shirts -- I have a DB that serves as a template for several customers. Each customer may have different ranges of sizes (one may have S,M,L and the other might also have XL,XXL). So the CATALOG table is:

CREATE TABLE [dbo].[T_Catalog](
[StlyeID] [int]
) ON [PRIMARY]
with data:
1
2


the SIZES table (filled in by the customer with all the size ranges they carry) is:

CREATE TABLE [dbo].[T_Sizes](
[SizeID] [int],
[SizeName] [nchar](10)
) ON [PRIMARY]
with data:

1,Small
2,Medium
3,Large
4,Xtra-Large

and the AVAILSIZES table would be:

CREATE TABLE [dbo].[T_AvailSizes](
[StyleID] [int],
[SizeID] [int]
) ON [PRIMARY]

1,1
1,2
1,3
2,1
2,3
2,4

Basically, then, we know that:

style 1 comes in Small, Medium, and Large
style 2 xomes in Small, Large, and Xtra large

WE know that, but getting SQL to tell us that is a major PIA!!

Now,

SELECT
t_Catalog.StyleId, t_AvailSizes.SizeID
FROM
t_Catalog
INNER JOIN t_AvailSizes ON
t_Catalog.StyleId = t_AvailSizes.StyleId

will give me a nice list of each item number with a separate row for each size number.

My questions are:

How do I get the size NAMES?

How would I get all of the sizes into a single row, so that there is a single row for each catalog StyleID (that's all I ever wanted to begin with)?

Is this the right way of doing this?

In reality I have about six columns that can contain multiple and variable items like this, and when trying to even think about resolving it all into a single record my brain tries desparately to crawl out my left ear.

Thanks for any help and information you can provide.

View 2 Replies


ADVERTISEMENT

How To Copy A Column(or Colums) From A Table In One Database To Another Table In A Different Databas

Oct 1, 2001

How do I copy a column(or colums) from a table in one database to another table in a different database

View 1 Replies View Related

INNER JOIN: Joining Two Different Colums With One Table?

Nov 15, 2004

Hello everyone,

I'm stuck on something here. Any help would be great. This is a relational database question.

I'm trying to inner join two columns of one table with one column of another. The follwoing code doesn't work, but I think you can see what I'm trying to do.



Procedure _Links_List
AS
CREATE TABLE #TempTable
(
LinkId int,
LinkCategory varchar(50),
LinkStatus varchar(50),
LinkName varchar(50)
)
INSERT INTO #TempTable
(
LinkId,
LinkCategory,
LinkStatus,
LinkName
)
SELECT
LinkId,
_SubCategories.SubCategoryName,
_SubCategories.SubCategoryName,
LinkName
FROM
_Links
INNER JOIN
_SubCategories ON _Links.CategoryId = _SubCategories.SubCategoryId
INNER JOIN
_SubCategories ON _Links.StatusId = _SubCategories.SubCategoryId
SELECT
LinkId,
LinkCategory,
LinkStatus,
LinkName
FROM
#TempTable



Also, I know how to do this if I had seperate category tables for each category (LinkCategory, LinkStatus). For examlple:



Procedure _Links_List
AS
CREATE TABLE #TempTable
(
LinkId int,
LinkCategory varchar(50),
LinkStatus varchar(50),
LinkName varchar(50)
)
INSERT INTO #TempTable
(
LinkId,
LinkCategory,
LinkStatus,
LinkName
)
SELECT
LinkId,
_Links_Categories.CategoryName,
_Links_StatusCategories.StatusName,
LinkName
FROM
_Links
INNER JOIN
_Links_Categories ON _Links.CategoryId = _Links_Categories.CategoryId
INNER JOIN
_Links_StatusCategories ON _Links.StatusId = _Links_StatusCategories.StatustId
SELECT
LinkId,
LinkCategory,
LinkStatus,
LinkName
FROM
#TempTable


I know the above works but I'm trying to figure out how to have just one category table and one subcategory table for all of my categories of all my tables.

Table_Categories: CategoryId (Primary Key), CategoryName

Table_SubCategories: CategoryId, SubCategoryId (Primary Key), SubCategoryName

So instead of having to create a new table for every category and all the procedures for them for all my tables, I want to be able to just use these two tables.

If anyone knows how I go about this, especially when a table uses two category columns, I Thank you.


Alec

View 3 Replies View Related

Is There A Way We Can Implement Business Logic By Storing It In Colums Of A Table And Calling It?

Jul 20, 2005

What is the way we could implement a business logic from a Table bystoring it statemnnets in a colums and defining an execute sql toexecute it.Some legal requirements make it diffcult for us to createmodify stored procdures so Iwant to have a process where we create newrows in a table and execute it to execute business logic.All views are welcome.Havin g one table two tables different approaches to store ststementsand execute them....Case logic how to implement it?Flags in the tble colums in the tablesetcThanksAjay Garg

View 1 Replies View Related

Transact SQL :: Insert Values From Variable Into Table Variable

Nov 4, 2015

CREATE TABLE #T(branchnumber VARCHAR(4000))

insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)

[code]....

I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.

View 6 Replies View Related

Rows As Colums

May 8, 2007

I know this is possible in DB2 and Oracle, but what about for SQL-server 2005
   
1)      select X number of rows from table1 
2)      I need  colums for each row of table1 in a new table
3)      As such,  Select   (select * from X where x.id = @ID), a,b,c    from table Y where y.Id = @ID
And I dont want to use IfExists.
Thanks
DK
 

View 1 Replies View Related

Query Across All Colums

May 18, 2004

Hi folks. This is my first post around here.
So I want to query a select on a table. Is there a simpler way to match the where clause with all columns than referencing every single column in the where clause?

Thanks

Shabassa

View 12 Replies View Related

Updating Colums

Nov 20, 2006

Hello all.

I have a column named 'RELATED' of type text in a table. I want to update the contents of this colums...........but still keep what is currently stored in it. Basically i want to add some text onto the end of the string it currently contains.

Any help would be much appreciated.

View 1 Replies View Related

Pivoting Row Values Into Colums

Oct 11, 2004

I’d like to get some data which includes month values bound to a data grid. The data is stored in a table like so:

Measure Month Value
A June 10.00
A July 9.00
A Aug 11.00
B Jun 100.00
B Jul 98.00
B Aug 99.00
C Jun 0.75
C Jul 0.8
C Aug 0.91

I need to report the data like this:
Measure Jun Jul August
A 10 9 11
B 100 98 99
C 75% 80% 91%

This was simple in classic ASP. Just use two recordsets, create a new table cell for each month using the first recordset then use the second recordset for each row.

But is there a way to “Pivot� or rotate the data so I can use the DataGrid? It only seems possible if each month has its own column field in table. Each month add a new column.

I can restructure the database, if needed.

I thought about creating a Cube, but that seems to have its own limitations. For example what if I want to add a Column for Quarter and year totals? I don’t think it’s possible to show multiple planes like that in an query of a cube.

It seems that this might be resolved in the presentation layer or the data layer. Any Suggestions?

View 1 Replies View Related

Convert Rows To Colums

Aug 26, 2004

I am getting this from my client
TableA
StorID LineNumber Text
30000 1 ClientName
30000 2 ClientSurname
30000 3 3333333
30000 4 20-05-2004

I actually want this to look like
TableB
StoryID Name Surname Policy Date
30000 Name Surname 3333333 20-05-2004

Can you help with the script to convert this to one record with many fields (as in TableB) if this is

View 4 Replies View Related

Bcp Command To Give Colums

Jan 8, 2004

Can someonte tell me that if i
bcp bda..mytable out c:discounts.xls -c -p , how can I put the first row as my column names since I get only data

View 2 Replies View Related

Repated Colums Returned

Jul 23, 2007

Hello All, first time post here and really stuck on this issue.

I have 4 Tables that i am Joining Across. Each one of the tables has around 800,000 Rows in it. I am Joining them all on a field Called MDRReport Key.

my query looks like this

SELECT Mdrfoit.MDRReportKey, Mdrfoit.DtReport, Mdrfoit.DateReceived, Mdrfoit.EventType, Mdrfoit.AdverseEvent, Mdrfoit.ProductProb, Mdrfoit.ReportOccup,DevTable.BrandName, DevTable.ManName, DevTable.ModelNumber, DevTable.ProductCode, DevTable.DeviceFamily, TextTable.Text

FROM Mdrfoit
INNER JOIN DevTable ON Mdrfoit.MDRReportKey = DevTable.MDRReportKey
INNER JOIN TextTable ON Mdrfoit.MDRReportKey = TextTable.MDRReportKey LEFT OUTER JOIN probLink ON Mdrfoit.MDRReportKey probLink.MDRReportKey

WHERE Mdrfoit.DateReceived BETWEEN '1/1/1995' AND '1/1/2007') AND
AND (DevTable.BrandName LIKE '&PLV%')

My problom is that when i do this i get back 3 rows in my result set for every 1 result that i want So ill get like

2615681/24/200000:00.0MNY114PLV-100RESPIRONICS, INC.35001CBK
2615681/24/200000:00.0MNY114PLV-100RESPIRONICS, INC.35001CBK
2615681/24/200000:00.0MNY114PLV-100RESPIRONICS, INC.35001CBK


So my result set is on average 3 time larger then its suppose to be, This is also causing my query time to be very very slow.

There are no Duplicate entries in the database where all of the fields are the same. I am confused on if this is a problom in my query. Any advice would be great






"When you have everything to loose and nothing to gain, All-In is not an option"

View 1 Replies View Related

Validating And Updating Colums

Jul 23, 2005

Hi all,I am a newbie to sql and I need your help.I want to update column (email) from one table to another validating theCustomerid column in both table. Update the email address in productiontable with the email address in temp table if the customerid is same in bothtables.What would be the query?Thanks,

View 3 Replies View Related

Matrix Subtotal But Not All Colums

Feb 21, 2007

Hello,

I have this problem that I do not now how to solve it:

I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).

If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.

Nr. Of Orders Nr. of Lines Average Delivery Time

Open Order 10 20 3.2

Closed Order 15 30 5

Total 25 50 8.2 (it should be, for example, 4.5)

Is there a way in which I can tell the system to not calculate the total for the third column?

If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.



Thanks to all!

Andrea

View 4 Replies View Related

Joined View Does Not Contain All Colums

Jul 20, 2006

The joined view is named "dbo.viewExecView" and is like:

SELECT Bank_No, data_center
FROM [ALPHASQL2000].ev_db.dbo.Bank

The new view that joins to the above view is like:

SELECT bank.BankID, evBank.data_center AS DataCenterID
FROM dbo.Bank AS bank INNER JOIN
dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)

Note: The data_center column (an int) was recently added to the Bank table in the linked ev_db database and it shows up there. It also shows up in the view "dbo.viewExecView". It does not appear in the new view that joins to "dbo.viewExecView". And when I run the 'new' view, I get an Error Message: Invalid column name 'data_center'.

I've tried to simplify this as much as possible while still including the pertinent information. Any help very much appreciated, I am currently stumped.

Regards,

Joe

View 4 Replies View Related

Stacked Column Bar With Negative And Positive Value. Cannot Cross At Zero

Jul 27, 2007



I've a stacked column chart (vertical) both with positive and negative value.

I want an horizontal line only for the value = 0.

I tried to add a serie and to plot it as line, but I see it in the legend and I don't want to see it.

Can I show the gridlines only for the value 0?
Simona

View 1 Replies View Related

Setting The Legend Color On A Stacked Bar Chart

Jan 17, 2007

I am trying to develop a report that will show a 100% stacked bar graph for many teachers. Each teacher will have there own graph. The series in the report are the percent of scores given on a test, with each test in a group. Scores are given on a scale of 1-4. Most of the time a teacher will have all fours scores in there data; however it is possible to only have 2 or 3 of the scores. How can I set the legend up so that all scores across all the graphs show the same color for the same data series? Right now I get something like this.
First Graph
4: Lime
3: Purple
2: Blue
1: Green
Second Graph (no values for 1 or 2)
4: Blue
3: Green

I need the second graph to be
4: Lime
3: Purple

View 5 Replies View Related

Reporting Services :: Stacked Bar With Line Marker?

Jul 16, 2015

I have a stacked bar with Line marker. that shows the trend contribution. My concerned is the line marker was behind the stacked bar which it should be at the front or before the stacked bar. how to fixed this in SSRS and Report builder. The line marker is the finished good qty while the 3 other legend WIP, FG and Scrap is the Percentage.

View 12 Replies View Related

Label Stacked Bar/column Chart With Percentages

Mar 11, 2008

Hi,

I need to label stacked bar /column charts with the percentage of each series at each axis point.

For example, if I have the following values in the chart:

Series 1 Series 2
Axis point 1 50 25

Axis point 2 12 12



I need the labels to read: Series 1 Series 2
Axis point 1 67% 33%

Axis point 2 50% 50%


It would seem that I need to format the data point labels to read something like: sum (data.label)/sum(data.label, "axis point")

Any ideas would be appreciated. Thanks in advance for your elegant solutions

View 3 Replies View Related

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure

Aug 29, 2007

which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View 1 Replies View Related

I Want To Join Two Colums From Two Differet Database

May 1, 2008



Hello I have a Source database and a Target database.

I want to join one table from the source to the other table in the target.

Please can some one write a sql query for this.

i gues its something like

select tablesource.col,tabledest.col
from database..tablesource,database..tabledestination

Ok One more question is where do I execute this Query in which database.. IF at all its possible to this.

View 6 Replies View Related

Stacked Column Chart Label Position Problem

Feb 26, 2008

I have a Stacked Column graph and when we set the Position of the Point Label to be "Top Center", the Point Label always stays in the center of that particular Bar.

On a easier, basic Bar chart, the Point Label setting works correctly.

Has anyone else had a similar issue or know if this is a known issue ?

View 2 Replies View Related

Make The Series Stand By Themselves In Stacked Column Graph

Aug 29, 2006

I'm making some reports that are made for getting printed. I currently have three groups of reports, each group having four charts (four A4 papers/group). To cut down on the sheer number of papers on the boards I've been asked to merge these charts so that we'll have all three groups in one chart. This works fine for three of the charts (two line charts and one column chart), where the results gets neatly organized in group order. Where I ran into problems was the last chart..

The last chart is a stacked column chart. With one group, it works quite well; the x-axis is by year/week, and for each week there's a bar where the lower part is "done" and the upper part is "failed".
Now when doing this graph with multiple groups (groups as series), it doesn't put the different groups beside each other as I would've wished, instead it piles them on top of each other as if it was a 100% stacked column.

So the question is; is there some way to make a stacked column graph with multiple series that are one stacked bar per group instead of having all of them stacked on top of each other? Am I just missing some small detail here?

View 2 Replies View Related

Help Required On Mouse Over Display On Stacked Column Charts

Aug 9, 2007

Is there a way to get markers on the top of each graph of a stacked column chart?
Also i need to display a label when the mouse points over each of the marker, as well as each partition of the graph. Getting the tooltips to work might b a help but it is not working for me. I got a reply that enabling the mouse over in the report viewer might help, but i dont know how to enable

View 1 Replies View Related

Reporting Services :: Using If Expression For Visibility For Stacked Column

Oct 13, 2015

I have a stacked column with 2 series("phones", "no phones") on the categories, What I am trying to do is to show only one of the series in each category,I used the visibility option in the series properties, and chose show or hide based on an expression using the expression below if(Fields!PENETRATIONSERIES. Value="no phone",FALSE,TRUE) i get no graph , But when I switch the true and false value, the graph show with the two series there.

View 3 Replies View Related

Order By Colums Asc Does Not Work In SQL SERVER 2005

Feb 12, 2008

Hello
 
Iam usng sql server 2005 database
 
I had this table
 
CREATE TABLE [dbo].[Companies](
[CompanyID] [smallint] IDENTITY(1,1) NOT NULL,
[Company] [nvarchar](40) NOT NULL,
 
I entered Arabic Names
 
so I write this query
select * from companies order by company asc
--and this one
select * from companies order by company
 
and I try to order desc
 
but the row didnt appear out ordered
 
and the big prtoblem happened when I try to update a company Its go to the last row when it appears
 
please help me
 
thanks

View 9 Replies View Related

How To Dynamic Specify The Colums In Full-Text Search?

May 14, 2004

My problem is simple: i want to dynamic specified the columns in the ContainsTable, this is possible? Please see the example.


Declare @Test1 int
Declare @Test2 int
Declare @Query varchar(50)
Declare @Temp varcahr(50)

--Test
Set @Test1=1
Set @Test1=0
Set @Query='something'

--Add the column to put in containstable
IF (@Test1=1)
Begin
Set @Temp='ID'
End

IF (@Test2=1)
Begin
Set @Temp= @Temp + ',Name'
End

SELECT *
FROM
<table>
INNER JOIN
CONTAINSTABLE (<table>, @Temp, @Query) AS KEY_TBL
ON <table>.ID = KEY_TBL.[KEY]



Thanks

View 1 Replies View Related

Showing Colums And Rounding 2 Decmial Places

May 8, 2007

Okay I have a problem and I have no idea to do it. I need the SQL syntax to come up with this:
the formatting is messed up though


a query to display the department name, location name, number of employees, and the average salary for all employees in that department. Label the columns dname, loc, Number of People, and Salary, respectively. Round the average salary to two decimal places.

DNAMELOCNumber of PeopleSalary
-------------------------------------------------------------------
ACCOUNTINGNEW YORK3 2916.67
RESEARCHDALLAS52175
SALESCHICAGO61566.67

View 2 Replies View Related

Reporting Services :: How To Remove Line Character In Stacked Bar Chart

Sep 21, 2015

I have a stacked bar chart which i notice a vertical line char. how can i removed this vertical line? I already modified the properties under smart labels under calloutlineanchor, calloutlinestyle, calloutstyle to None but still getting this vertical lines. another things is the data label as you can see in the the first column the 1.8% should be place under the gray color but its already shown before the orange color.

View 3 Replies View Related

Reporting Services :: SSRS - Show Percentage Or Ratio In Stacked Bar

Nov 20, 2015

Sample Query :
SELECT 'A' [Type], 1 AS [NUMERATOR]     , 2 AS [DENOMINATOR]
UNION ALL
SELECT 'B' [Type], 2 AS [NUMERATOR]     , 4 AS [DENOMINATOR]
UNION ALL
SELECT 'C' [Type],  3 AS [NUMERATOR]     , 6 AS [DENOMINATOR]

[Code] ...

I need to show the ratios beside each bar (shown in red) as shown in the above diagram.

View 2 Replies View Related

Reporting Services :: Combining Stacked And Clustered Column Chart

Sep 27, 2015

I need to generate weekly report for data usage for my c drive and d drive.the graph should be likefor a particuluar date, I want to display graph like this. even if I change secondary axis, my c: drive used and free space not coming in single column. it comes like c: drive used space, on top of it d: drive used space and in adjacent column  c: drive free space, on top of it d: drive free space.I don't want to add two category one for date and another for driver names.

View 6 Replies View Related

Auto Fill In Colums Trough Foreign Key Relationship

Jun 10, 2004

Hi,

I have a table users where there is a user_id and an department column.
Also i have a table called KRS where there are the same columns, when a userid is given i want to auto fill in the departmentid,

Can someone help me with this?

Cheers Wim

View 2 Replies View Related

Reporting Services :: Decrease Space Between Columns In Stacked Column Chart

Sep 9, 2015

I have another query regarding the graph/chart that i made in report builder.  May i know on how to lessen the space between a column in my stacked bart chart.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved