i have 2 columns like
Col1====col2
username=======saif====mr@hotmai.com
email=======saif@hotmail.com
name======saifullah
I want to get these records from sql like
username email name
saif saif@hotmail.com saifullah
________________________________-
how can I achive this
Muhammad Saifullah
I am interested in changing the way that data is displayed in my result set.Essentially I want to display a selection of rows (1 to n) as columns, the following diagram explains my intentions.Perhaps one of the greatest challenges here is the fact that I do not have a concrete number of rows (or BIN numbers). Each stock item could be stored in one or more BINS, which I will not know until running my query.
View 2 Replies View RelatedHi to all,
As I am going to deal with a huge number of database tables, I thought that drawing their diagrams will be the most professional way of keeping track of what I am doing. So In Enterprise Manager using the Wizard I made it to be drawn nicely.
But I have a problem now. I see that the relations defined among the tables are represented correctly but the line connecting the two table is drawn randomly. I mean the starting point of the line doesnt start from the column having the primary key and doesnt end near to the column having the foreign key. Viewing my diagram I want to see the lines to start and end showing the 2 related key columns.
Is there a way to accomplish this like setting an option, clicking somewhere or should I try to drag the lines to manually?
Thanks in advance
Hi guys,
The below query should produce all those customers who are in 'orginalerrorfiles' but not in 'hopefullyworks2'. 3 fields will be used as the check to make sure that the rows, that r identical won't be displayed but those that r different will be displayed.
Instead of doing that it just display everything in the table called 'orginalerrorfiles'.
Note 'orginalerrorfiles' consist of 141455 rows
'hopefullyworks2' consist of 134784 rows
select o.card_no
,o.ref_no
,o.tran_val
from OriginalErrorFiles o
where exists (select h.card_no
,h.ref_no
,h.tran_val
from hopefullyworks2 h
where( h.card_no = o.card_no and
h.ref_no=o.ref_no and
h.tran_val = o.tran_val
))
I have just installed SQL Server 7 on a server. Everything was installed fine without any errors. The only problem occurs when I use Enterprise Manager, I right click on a table in the Northwinds database to open table and return all rows. The form pops up and no rows appear. Nothing in the table form works. I can view the records from Query Analyzer, and can also view them through Enterprise Manager when using a workstation client. This problem only occurs at the server. Any thoughts.
View 1 Replies View RelatedHi All,
i couldn't find how to set up the number of rows displaying on each page?
i wanna each page display 20 rows.
Thanks
Nick
I have two tables A and B, A has 8000 and B has 8122 records. I want to see what records are missing. I tried EXCEPT and it returned zero rows. I used where non exists also still no records.
View 5 Replies View RelatedI have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
View 28 Replies View RelatedI've created a cube and it processed fine. The calculate command is there. The measure that I'm attempting to run in the SSAS/Visual Studios browser is simply a count rows measure. When I drag the measure to the window, it says no rows available. If I click on the filter that allows nulls, the only change it makes is that it goes from no rows available to "NULL".
View 4 Replies View RelatedI need to hide a column just for certain values from a table like contract decorators I tried using
=IIF(Fields!MainTrade.Value = "Contracts Decorator, Bricklayer", True, False)
But it just hid the column for all values, then i tried this but it doesn't seem to work (Think it's not correct though)
=IIF(Instr("Contracts Decorator, Bricklayer","," & Fields!MainTrade.Value & ",") > 0,true , false).
Below is the table and the highlighted column i am trying to hide just for those two fields. The table has a page break so that it would be displayed on a different page for each value, but i'm not sure it is possible as i have only done it for integers and rows before.
suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"
I want's some thing like
field1 rowcount
aaa 5
bbb 6
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.
Result:
PID1
Hello,
I need to concatenate a column from multiple rows into a single column in a new table.
How can I do this?
SID NAME PGROUP
------------------------------------------------------------
3467602 CLOTHINGACTIVE
3467602 CLOTHINGDANCE
3467602 CLOTHINGLWR
Need to have
SID NAME PGROUP
------------------------------------------------------------
34676 02 CLOTHING ACTIVE , DANCE, LWR
THANK YOU
Hi I'm having a bit troubble by creating a SQL-sentence which substract the newest row from the second newest row in the same column. The table looks like this:
Pricecalcid Date Price Itemid
2000 2006-12-12 3000 100
2488 2007-10-11 2800 100
3100 2008-08-07 2500 100
What I need is that the largest "Pricecalcid" that is 3100 equals "Price" 2500 and the second largest "Pricecalcid" eqauls 2800 results in a pricecalculation that substracts 2500 from 2800 for "Itemid" 100.
How do I do that?
Thanks
Morten
hi
i have a query that returns 1 column 5 rows which i want as 1 row 5 columns
SELECT columna
FROM atable
would become??
thanx
Liju
How to put two rows of data in one column?
from
dealID dealNO
1 888
1 999
to
dealID dealNO
1 888,999
you can't use create permission, so can't create storedprocedure.
Pls help.
Hello,can I do this via SQL:example: tbltest has 5 rows:col1===ACFMRWhat I want is this:result:===============A, C, F, M, RDo I really have to go through the rows per SP? I could do this:SELECT UDF(col1)FROM tbltestAin't there a more simple way, maybe theres a T-SQL-command ?thxcandide_sh
View 6 Replies View RelatedI want to convert the row value as column name
example:-->
value ratio
3 4.166666666661 1.315789473680 00 0
To :->
value ratio ratio1 ratio2 ratio3
3 4.166 1.315 0 0
any ideas?
I have a table that has a column for each month and I want to use a view to convert each row into 12 rows with a one month column. The month column will have 1 - 12 in it depending on the month. I need to convert it this way to push it into an Essbase cube. I know I can use the union operator to do this, but I would rather not read the table 12 times. Is there a way to do this just reading through the table once? My Hyperion Essbase book gives an Oracle example for swapping rows and columns by using a decode function. Is there a similar function in SQL Server?
View 2 Replies View RelatedCan anyone help me retrieving that result.
take an example
EXEC-ID JOB-CODE
0001 20
0001 63
0001 03
i want result in the following format:
EXEC-ID JOB-CODE1 JOB-CODE2 JOB-CODE3
0001 20 63 03
Came across one scenario not able to find out how to do it..
Below is the table data
ColA
1
-1
2
-2
Need output in below way
ColA ColB
1 -1
2 -2
Hi, I need to update column week14 in table PastWeeks with data from Eng_Goal and then result of some calculation from table AverageEngTime in the row Goal and Used respectively. I used the following and was not successful. Please advice. Thank you.
Update Pastweeks
set
week14 = (SELECT Eng_Goal,((Mon_Day + Mon_Night + Tue_Day + Tue_Night + Wed_Day + Wed_Night + Thu_Day + Thu_Night + Fri_Day + Fri_Night + Sat_Day + Sat_Night + Sun_Day + Sun_night)* 100/168) FROM AverageEngTime where Shifts = 'Average')
where Weeks = ('Goal','Used' )
i want to split the value of one column into multiple rows. based on comp capacity. pl help me in writing query
Actual table
Product IdProductQtyCompCapacity
1 8000 5000
2 10000 5000
4000
4000
Resultant table
Product IdProductQtyCompCapacity
2 50005000
2 50005000
1 40004000
140004000
I have a table with following sample data:
**CategoriesIDs RegistrantID**
47 1
276|275|278|274|277 4
276|275|278|274|277 16261
NULL 16262
NULL 16264
NULL 16265
[code]....
I need to know :
1). which category has how many regisrtantids (like 277 has how many registrantids) 2). group the registrants by category so that i can find which registrants are in category 277 for example)
Do I need to create a function which generates a table from csv ? I have created a function but not sure if it will work in this situation with IN clause.
I am looking to transpose a row into multiple columns. I have a member's data who might be associated with multiple labs for that one member. Instead of having multiple records for that member, I would like to have one record per member with multiple rows for the lab data.
View 16 Replies View RelatedHello, This is my first post so please be kind. I have been attempting to convert a query I built in MS Access for use in MSSQL 2000, the syntax for these is different so I was frustrated to find out I could not use the access query.
I have 4 columns one containing a user Id and the others costs, I wish to total the costs per user ID at the end of each row.
So far I have managed to convert about half of my access query, this gives mev the clientID's and costs in columns but I cannot for the life of me get the costs in a total. It's annoying because my access query works perfectly.
This is my Access query:
SELECT DISTINCT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Room_Facilities.FacilityCost, Rooms.CostPerNight,
Rooms!CostPerNight*Nights_Stayed+Holiday_Bookings!Booking_Cost+Room_Facilities!FacilityCost AS TotalCost,
[TotalCost]*17.5/100+[TotalCost] AS [Total+VAT]
FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID) ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;
And this is what I have been able to salvage into MSSQL format:
SELECT
Holiday_Bookings.ClientID,
Holiday_Bookings.Booking_Cost,
Rooms.CostPerNight,
Room_Facilities.FacilityCost
FROM
Rooms
INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)
INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID)
How can I total the three columns and add the tax?
Mike writes "Hi,
I am a beginner with TSQL and I hope this is not a silly question :-)
Lets say I have a table with 2 columns, 1 a primary key identity field with increment 1 and the other a char.
EG:
ID ANIMAL
---------
1 CAT
2 DOG
3 PIG
4 RAT
5 PIG
6 DOG
7 DOG
.
.
And so on with many entries
How do I return a selection of rows that have the contents of the ANIMAL field matching 1 or more times
EG:
From above table I want to return rows 2,6 & 7 and 3 & 5 ONLY and not 1(CAT) and 4(RAT) because they only occur once.
In my real life situation I have unknown numeric data in field 2 but the principal is the same.
How do I do this?
Thanks in Advance
Mike"
I am struggling on this issue and was hoping if anyone out there canhelp me.Here is the setup: I have a table with the following data:TableName: TranDetailMerchID ItemName Price------------------101 A 5101 B 3.5101 C 0102 B 7.6102 C 4102 E 65102 G 4103 K 35Table DesignMerchID intItemName varchar(50)Price floatWhat I would like is a report that looks like thisMerchID A B C E G K101 5 3.5 0102 7.6 4 65 4103 35This report can change on every run depending on data in tableTranDetail. The column name in report depends on ItemName in tableTranDetail. As seen in the above report, there is no data for Items D,F, H, I, J and hence they do not show up in the report.What I need: Code for a stored procedure that can get me this data.Thanks for your help...DBA in despair!
View 7 Replies View RelatedI have a column that has text delimited by a percent sign that I wishto turn into rows.Example:A column contains ROBERT%CAMARDA, I want to turn that into two rows,one row with ROBERT and antoher row with CAMARDA.I will have source rows that have zero, one, or many percent signdelimiters that will correspond to that many rows (One percent signwill create 2 rows, 2 percent signs will create 3 rows and so forth).Any thoughts?TIARob
View 1 Replies View RelatedHi Friends,How to transpose the columns into rowsi want to convert the table which looks like thisID Name HomePhone WorkPhone Email1 test1 678364 643733 Join Bytes!2 test2 678344 643553 Join Bytes!to a table which should look like thisID Name Device1 test1 6783641 test1 6437331 test1 Join Bytes!2 test2 6783442 test2 6435532 test2 Join Bytes!Thanks in AdvanceArunkumar
View 1 Replies View Related
Hi
I have an old db I'm converting with SSIS.
In the old db I have an Log-table wich is in this format:
ID,CaseID,Note
each CaseId can have several rows.
Now I want to merge the Note Row into one row based on the CaseID.
So that I get One Row per CaseID with one bignote.
I have been looking into Pivot, but just dont seem to get it working.
Any Ideas?
Hi,
I have issue about how to pivot and merge some rows into one column.
I have the table name : tableA with columns and records as below shown:
Cola Colb
------ ------
A A1
A A2
A A3
B B1
B B2
The request is to display the query like this :
Cola Colb
------- -----------------------------
A A1A2A3
B B1B2
I get stuck with my query below :
select Cola, ???
from tableA
group by Cola
Do you have idea how to achive this with TSQL?
Thanks.
Kasim
Hi all,
I have a table in sql server in this format.
ID Color Density Value
1 W 1.5 100
2 X 1.5 100
3 Y 1.5 100
4 Z 1.5 100
5 W 1.6 98
6 X 1.6 98
7 Y 1.6 98
8 Z 1.6 98.1
Now I want to display the data in this format
ID Color Density Value ID Color Density Value ID Color Density Value ID Color Density Value
1 W 1.5 100 2 X 1.5 100 3 Y 1.5 100 4 Z 1.5 100
5 W 1.6 98 6 X 1.6 98 7 Y 1.6 98 8 Z 1.6 98
Basically I want this format so that I can bind this to the datagridview directly and then use the same for a two way binding (saving) back to the database.
How do I get this result format using a query? Thanks in advance.