Select Data By Vertical Dimension

Mar 12, 2007

Dear everyone,
i have a table like below:
id title
1 a
2 b
3 c
and i want to get data from the table above with this format:
column1 column2 column3 column4
1               a          2         b
3               c

How can i do this with select statement or inner join?? or an posible way , please help me

Thnks

View 1 Replies


ADVERTISEMENT

Select Data By Vertical Dimension

Mar 12, 2007

Dear everyone,
i have a table like below:
id title
1 a
2 b
3 c
and i want to get data from the table above with this format:
column1 column2 column3 column4
1               a          2          b
3               c

How can i do this with select statement or inner join?? or an posible way , please help me

Thnks

View 3 Replies View Related

Analysis :: Add Dimension To Cube Dimension Without Any Relation In Dimension Usage

Oct 26, 2015

When i add a dimension to the cube dimension without any relation in my dimension usage to any measure group my units are going down.However when i remove the dimension from the cube am getting the correct values.

View 4 Replies View Related

Could Anybody Tell Me How To Convert Vertical Data Into Horizontal Data?

Sep 2, 2005

Could anybody tell me how to convert vertical data into horizontal data?I have a one-to-many relationship in sql server 2KProduct, ProductAccessory, one Product has many ProductAccessories.My Table design is like this:Table Product{   ProdId int,   ProdNameId int,   ....}Table ProductAccessory{   ProdId int,   AccNameId int,   AccUnitId int,   ....}Because one Production has at most 4 ProductAccessoryI want to use a SELECT statement OR function to return ProdId, ProdNameId, AccNameId1, AccUnitId1, AccNameId2, AccUnitId2, AccNameId3, AccUnitId3, ....Any help will be appreciated! Thanks a lotJoseph

View 3 Replies View Related

Converting Vertical Data To Horizontal

May 25, 2006

I have a table as follows
opendate (datetime) callnumber (int) closed (bit)

I want to find how many calls were opened today and of those how many are closed

I have come up with the code below but again am looking for
1. a more elegant solution
2. a way to generalise this to show the same information for x number of days




create table #holdit1
(opencount int)

create table #holdit2
(closedcount int)

insert into #holdit1
SELECT count(*) as opencount
FROM [dbo].[problog]
WHERE datediff(dd, opendate, getdate()) = 0
AND closed = 0
group by closed

insert into #holdit2
SELECT count(*) as closedcount
FROM [dbo].[problog]
WHERE datediff(dd, opendate, getdate()) = 0
AND closed = 1
group by closed


select #holdit1.opencount AS CallsOpen, #holdit2.closedcount AS CallsClosed, #holdit1.opencount + #holdit2.closedcount AS AllCalls
from #holdit1 cross join #holdit2 #holdit2


DROP TABLE #holdit1
DROP TABLE #holdit2



this gives me
CallsOpen CallsClosed AllCalls
----------- ----------- -----------
1 3 4

View 4 Replies View Related

When To Make A Vertical Partition In Data Warehous

Jun 5, 2008

Hi All,

I am trying to understand, when would I do a vertical partition in a Dimensional Data Warehouse ? What are the things I need to consider, before I take the decision?

Necessity is the mother of all inventions!

View 7 Replies View Related

How To Convert Data Horizontal To Vertical In Server

Jul 24, 2015

Month Discount
Expenses GST
<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d14582be-eecd-4def-be7e-0cfe00b13c80" id="4279c12f-e03f-4b38-9fc8-eb1a991c25ac"><gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="7f0da910-3775-4ee4-821e-1a0e7ee2ce0b"
id="92ce074f-ee0b-4794-839b-ee50c88d380c">ServiceCharge</gs></gs>

[Code] ....

View 2 Replies View Related

Transact SQL :: Vertical Column Names With Data?

Jul 14, 2015

I have a table as below and need getting the desired result as below

Col1 Col2 Col3
A B
C

---desired result

t1 t2
Col1 A
Col2 B
Col3 C

[URL]

View 3 Replies View Related

Vertical Partition Of Table With TEXT Data Type

Feb 11, 2008



Hi
Column with TEXT datatype is not stored in the same data row any way. I am wondering if there is any performance gain to put it in a seperate table. Thanks

View 4 Replies View Related

Reporting Services :: Vertical Axis Data Labels Repetition / Duplication

Sep 15, 2015

When I tried to create a bar chart using SSRS 2012, the vertical axis values are repeating for smaller data sets values. It's only happening when the data labels are below 5, when the data is above 5 this chart represents data fine.

I tried specifying the custom intervals and this option all together eliminated the  bar for value 1, instead it only showed the value 1 as text on the chart.

I tried changing the data interval type as number and the data type is of Integer, these are counts which I am showing in the chart.

View 3 Replies View Related

Power Pivot :: Select Dimension By Slicer

Aug 18, 2015

I would like to provide users a way to dynamically select the dimension by which they analyse data in a pivot table. In a simple example, my fact table has 3 columns: product type, colour, volume. The pivot table consolidates the volume, and a slicer allows the user to display the sum of volume either by product type or by colour.

The solution I have come to so far involves creating a table with a single column that contains all the single values of product type and colour. There are 2 relationships between the fact table and this lookup table: one active on the product type column, one inactive on the colour column.

A 3rd disconnected table provides a way to select which of the 2 relationships is the active one for the consolidation metric. The example is available here.

View 2 Replies View Related

Deadlock In View With Select Union - Creating A/S Dimension

Jul 23, 2005

I've got a view that creates a parent child relationship, this view isused in Analysis Services to create a dimension in a datastore. Thisquery tends to deadlock after about 10 days of running smoothly. Onlyway to fix it is to reboot the box, I can recycle the services for aquick fix but that usually only works for the next 1-2 times I call theview.This view is used to create a breakdown of the bill-to locations fromContinent-Global Region-Country-Sub Region-State/Province- City-ZipCodeYes, I know that sounds crazy, but it was a requirement.So why would I get a deadlock on a SELECT Query? Is there a way to setthe Isolation level to Repeatable Read for a view?Here is the view code:CREATE View dbo.vwBillToas-- US ZipCodeSelect 'Parent'=z.City+' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')',z.Zipcode_WK as 'Child',z.ZipCode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToWherez.US_Region_wk IS NOT NULLGroupby z.City,z.ZipCode_WK,US_Region_wk, z.State_shrtUnion--CitySelect 'Parent'=z.State_Long+' ('+cast(IsNull(z.US_Region_wk,0) asvarchar)+')',z.City as 'Child',z.City + ' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')' as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zWherez.US_Region_wk IS NOT NULLGroupby z.State_Long,z.City,z.State_shrt,z.US_Region_wkUnion-- Canada ZipCodeSelect 'Parent'=z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') +')',z.Zipcode_WK as 'Child',z.Zipcode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion--CitySelect 'Parent'=z.Province_Long,z.City as 'Child',z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') + ')' as'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion-- Canada ProvinceSelect 'CANADA',Province_Long,Province_LongFromdbo.DIM_POSTAL_CODES_CANGroupby Province_LongUnion-- CountrySelect t.Region_NK,c.Country_Name,c.Country_NameFromdbo.DIM_COUNTRY cInnerJoin dbo.DIM_WORLD_REGION tOnc.Region_WK=t.Region_WKWherec.Country_Name Is Not NullGroup by t.Region_NK, c.Country_NameUnion-- SubRegionSelect c.Country_Name,sr.US_Region_Name,sr.US_Region_NameFromdbo.DIM_US_REGION srInnerJoin dbo.DIM_COUNTRY cOnsr.Country_wk=c.Country_WKGroupby c.Country_Name, sr.US_Region_NameUnion--RegionSelect sr.US_Region_Name,c.State_Long,c.State_Long+' ('+cast(c.US_Region_wk as varchar)+')'Fromdbo.DIM_US_REGION srInnerJoin dbo.DIM_POSTAL_CODES_US cOnsr.US_Region_WK=c.US_Region_WKGroupby sr.US_Region_Name, c.State_Long,c.US_Region_wkUnion-- ContinentSelect Null,Region_NK,Region_NK[color=blue]>From dbo.DIM_WORLD_REGION[/color]WhereRegion_NK Is Not Null

View 1 Replies View Related

Aggregate Data In Dimension Tables?

Jan 30, 2008

I'm still fairly new to cubes, so bear with me. I'm trying to figure out if I should include aggregate data (e.g. total employees per facility) in a dimension table or if I should use the finished cube to get the counts (MDX?). Thanks for any help that you can provide.

influent

View 1 Replies View Related

Data Starts To Repeat When I Add A Dimension.

Jun 27, 2006

Hello, I€™m creating a cube with Analysis services 2000 and SQLServer 2000 that has the following structure:
The fact table has a primary key and some measures:


Fact_Table

PKFact (the primary key)
Measure 1
Measure 2

And I have some dimensions, but I€™ll put here the one of interest, this dimension has a foreign key to the fact_table, this is because it is really a €œdetail€? of the fact table, the interesting thing here is that this dimension not only has a foreign key to the fact_table, but also is a parent-child dimension:


Dimension_Table

FKFact (the foreign key to the €œPKFact€? field of the fact table)
PKId (The id of the field for the parent-child relationship)
FKParentId (the foreign key to this same table, this is, the parent of the field)
TheLevel (the data€¦)

So, if I create the cube without the Dimension_Table (of course, I have more dimensions), the cube shows the data correctly, but when I include this dimension, the data of the fact_table starts to be repeated once for each row in the dimension table (once for each row that has the relation PKFact €“ FKFact of course), Somebody knows how I can avoid this data being repeated? I€™ve tried a lot of things but none works.. Please help!!

Thanks in advance.
Odin_Dark@yahoo.com.mx

View 1 Replies View Related

Data Mining Against A Cube With Time Dimension

Nov 21, 2006

We have a set of cubes and dimensions, and we're experimenting with data mining against the cubes (primarily for forecasting applications). We have a custom time dimension (which we call calendar), not generated by the BIStudio wizard. The dimension has year/month/day/hour/... attributes. But when I try to add this Calendar dimension to the mining structure as a nested table using BI studio, it only shows the Year attribute, not the others. Other dimensions seem to show all the attributes.

Is there something we've done wrong in defining our time dimension? What determines which attributes show up as available for selection in BI studio?

View 5 Replies View Related

Data Warehousing :: Dimension With Over 330M Records / Need Of Updates

Jun 22, 2015

I have a table that is increasing quite largely each day. By now, I have average 300 million of records over 2.5 years. Before we received our new interface, the data we received was aggregated and thus not that big.The problem is that the table is so huge that I cannot use the Slowly Changing Component. I was thinking about making a temp table where I load the incremental data before I load it into the final data mart table.Based on this temporary table I use a script to compare the temp data with the already existing data in the data mart. However, this requires a compare of each records (300 mil records).

View 3 Replies View Related

Analysis :: SSAS Dimension Data For Roles Not Working

Apr 21, 2012

I have 5 cubes, and hierachy defined for all cubes. for example:geography database with 5 continents as cubes and contries as dimensions.Now when i am doing security restrictions on my dimension ex: In USA dimension if i want only to give access to texas region then i should be able to see only texas cities. But i cansee all the states under USA even after selecting only Texas region under Dimension data tab inside ROles section in SSMS.I have tried security at database ,cube level as well as dimension level.But still not working.is that because of some wrong design of cubes or something related to database design.? I am not able to undersand that except roles everything in my cubes or datawarehouse is working fine without and defect in data.

View 2 Replies View Related

Moving New Data Into Dimension Tables Automatically Using Integration Services

Jul 25, 2006

I have an Integration Services package that loads new data into tables that are dimension tables wi my cube. The same situation exists for my fact table. If I perform an "Analysis Services Processing Task" for the dimensions ,cube and measures, will that move the new data into my cube or do I need to perform the "Dimension Processing Destination" data flow task prior to this? Is the initial processing task good enough?

thx,

-Marilyn

View 3 Replies View Related

Data Warehousing :: Populating Fact Tables With Surrogate Key From Dimension Table?

Sep 11, 2015

How do I correctly populate a fact table with the surrogate key from the dimension table?

View 4 Replies View Related

Analysis :: Browsing Measure Via Fact Dimension Showing Incorrect Data?

Sep 6, 2011

I have a Fact table that contains several degenerate string values that I have pulled into a Fact Dimension.

When I browse the cube and cut one of the measures by an attribute from the Fact Dimension, I am getting incorrect data.

In other words, when I query the fact table directly via SQL and apply the same filters, I see the data I am expecting to see. But cube browse with same filters yields different results.

How can this happen since the fact dimension has a 1:1 relationship with the fact table.

I do have the Dimension Usage configured properly.

Is this an aggregation thing? Attribute key thing? What am I missing?

View 3 Replies View Related

Analysis :: Data Masking For Dimension Attribute Based On User In SSAS 2014 Multidimensional?

Jun 15, 2015

I am trying to implement data masking based on user login and not sure why this is not working. I have the dimensions DimBrand, DimProduct and DimUser. I should mask the BrandCode with 'XXXX' nothing but in the report all the BrandCode should appear but few of the code will be masked if the user is not belongs to that group. I have a fact table FactProduct in this. In the cube I created all these 3 dimensions and the fact table. I created a new dimension DimBrandMask and I separated the code over there with a relationship with the actual DimBrand dimension. In the cube a reference relationship is set up with the measure group. Created a role with read access.

In the dimension data tab of role I put the below MDX to allowed set.

NonEmpty([DimBrandMask].[Brand Code].Members, (StrToMember("[DimUser].[Login Name].[Login Name].[" + UserName() + "]") ,[Measures].[Dim User Count]))

And in Denied Member set i put the below MDX

IIF( (StrToMember("[DimUser].[Login Name].[Login Name].[" + UserName() + "]"), [DimUser].[Access Right].&[False]), NONEMPTY( [DimBrandMask].[Brand Code].Members,(StrToMember("[DimUser].[Login Name].[Login Name].["
+ UserName() + "]"), [DimUser].[Access Right].&[False], [Measures].[Dim User Count])),{})

Note I created one measure group from the DimUser table and the measure [Dim User Count] is used in the above query.

I am expecting some result like below

Brand      BrandCode           Count
Brand1      b1                       6
Brand2     XXXXX                  5
Brand3     XXXXX                 10

View 9 Replies View Related

Filtering Out 1 Time Dimension Based On The Selection In Another Time Dimension

May 19, 2008

Hi!

Need some help building a query that does the following :

I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate)

In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031]

Both Time Dimensions are Year -> Month -> Day and have the same Members.

I have every option available, using calculated Members and/or Measures to do this.

The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above.

Appreciate all help!

Regards,
Stian Bakke

View 3 Replies View Related

Vertical Partitioning

Jan 4, 2007

Snehalata writes "does view for vertical partitioning improves the performance, since the view will have all the columns which exist in the original table(without partitioning?"

View 1 Replies View Related

Vertical Line

Dec 17, 2006

Hi
is it possible to set the vertical line to stretch from top of list box to the bottom even If the list box might grow at run time I want the line should stretch from top to bottom

View 5 Replies View Related

Vertical Merging

Aug 6, 2007

We can merge two horizontal cells but how to merge two table cells vertically, Means R1C1 with R2C1 to make R12C1?

Thanks lot...

View 1 Replies View Related

Using A Normalized (vertical) Table

Sep 24, 2007

I'm working on a couple projects and I've recently been trying to make everything fully normalized so updates are easier and I'm just wondering if there's a standard way to query and update normalized tables.
For example:
If I have table People with columns ID, FirstName, LastName, Height, Weight, ShoeSize, I can normalize that into two tables. Table People has ID, FirstName and LastName. Table PeopleDetails has PeopleID (FK), Property and Value. That way i can add more properties later right at the presentation layer if I like. Essentially I moved the data from being horizontal to being vertical.
But doing a simple search for people means I have to search the details table and return a LOT more records (one each for Height, Weight and ShoeSize) not to mention any more details I might add later. With a lot of details, it seems like your performance would take a big hit and your code would get really complicated as your looping through a vertical dataset to find the properties you want. Or is there some other standard way of doing that?
I'm just hoping that someone else has solved these problems and there's a standard set of functions out there for selecting and updating this kind of DB structure. Anyone?

View 6 Replies View Related

Horizontal To Vertical Array?

Nov 29, 2012

I'm running SQL Server 2008 Standard.I need to create a query that has data from multiple columns (Columns 1-6 with coresponding Date started and Date Completed data) displayed vertically, but also has the column name the preeceeding column to identify it...along with other data (Record number, status).

Record Number, Status, Column Name, Date Started, DateCompleted
1, Open, Column 1, 1/1/2012, 2/1/2012,
2, Hold, Column 2, 1/3/2012, 3/1/2012,
1, Open, Column 3, 2/5/2012, 4/6/2012,
3, Closed, Column 4, 5/10/2012, 7/25/2012,
2, Hold, Column 5, 3/9/2012, 4/1/2012,
1, open, Column 6, 10/10/2012, 12/12/2012,

View 5 Replies View Related

How To Convert Vertical To Horizontal Row

Jun 13, 2008

id name
--- -----------------
236 SERVICE REQUEST
236 HARDWARE
236 Desktop
336 Loan

id name
-- ----------------------------
236 SERVICE REQUEST/ HARDWARE/ Desktop/Laptop/ Loan

View 5 Replies View Related

Horizontal To Vertical Table

Jan 7, 2015

I have a table like this:

weight type factory1 factory2 factory3 factory4.... to factory 150

1kg goods 5.00 5.50 5.20 5.00...
2kg goods 6.00 6.20 6.15 6.30...
3kg goods 4.00 4.50 5.00 4.30...
...

and would like to extract data this way:

producer type weight price

factory1 goods 1kg 5.00
factory1 goods 2kg 6.00
factory1 goods 3kg 4.00
factory1.....
then

factory2 goods 1kg 5.50
factory2 goods 2kg 6.20
and so on for all factories.

I tried with UNPIVOT but it does not allow it (I'm using Navicat 8), saying "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near UNPIVOT...".

View 2 Replies View Related

Sql For Results From Vertical To Horizontal

Mar 15, 2008

Hi all

I dont want to use functions or proceedures but with select itself I want to get the above mentioned result. Can somebody help


SURESH IYER

View 2 Replies View Related

Horizotal To Vertical Display

Sep 29, 2005

Following is my table and its content:YearTargetedBudgetFirstQuarterSecondQuarterThirdQuarter-----------------------------------------------------------------------------------------------------20002500012000110001000200135000220002100020002002450003200031000300020035500042000410004000I want a query which returns the result in this format:Yr_Col1Yr_Col2Yr_Col3Yr_Col4----------------------------------------------------20002001200220032500035000450005500012000220003200042000110002100031000410001000200030004000There could be many work around, but what would be fasted single queryfor this?

View 8 Replies View Related

How To Create Vertical Text?

Jan 13, 2006

Hi,

Does anyone know how to format a text in a textbox or a table cell to show vertically?

Or, rotating a textbox with 90 degree angle? Thanks.

 

View 3 Replies View Related

How To Create Vertical Partioning

Feb 13, 2007

Hi All,

I setup a Transactional replication between SQL Server 2000 and SQL Server 2005 via
wide area network.

The intial snapshoot failed because run into problems on a table with column 379 columns.

How do ceate Vertical Partioning a table on Publication?

Thank you.
TJ_1

View 4 Replies View Related







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