Make A Bunch Of Rows From A Table Resemble A Column
Nov 23, 2005
Currently I'm using a UDF and a physical temp table to accomplish this
and I want to know if there's any way of doing it in a query or
subquery instead...
In TableA there are a bunch of rows:
InvitationID (PK) PartyID Partygoer
1 1 Jim
2 20 Bob
3 2 Frank
4 1 Robert
5 1 Pete
In TableB are rows:
PartyID (PK) PartyDate PartyName
1 1/1/2000 WildParty
2 1/1/2000 BoringParty
When a user runs a query to search for all parties on 1/1/2000 I want
the result to look like:
PartyID PartyDate PartyName Partygoer
1 1/1/2000 WildParty Jim, Robert, Pete
2 1/1/2000 BoringParty Frank
I'm hoping there's a solution to this.
Thanks,
lq
View 9 Replies
ADVERTISEMENT
May 20, 2008
I'm running SQL Server 2008 Feb08 CTP and I've got a tablix with column groupings and row groupings which works nicely, I'm a becomming a big fan of the tablix.
However sometimes the filter I have on a column group returns no records and the whole column group disappears.
Is there any way to make this column appear with but with empty cells as I have another tablix down the page with the same columns and I want the columns to all line up on the multiple tablix.
In Version 2005 I used to achieve the equivalent of a column group within a table by adding a list with filter to the cell, this meant the column always was shown, the tablix allows does this much more elegantly but I'll have to go back to the old method if I can't make the column remain.
Am I just missing a simple option?
Nathan
View 1 Replies
View Related
Nov 29, 2007
Hi,
Can anybody tell how to ristrict the number of rows per page in SSRS?
I have a table in my report.I know we have to use" =Int(RowNumber(Nothing)/25)" in group expression.But I already have 3 group expression in my table.Where to write??
Thanks.
View 9 Replies
View Related
May 24, 2008
Hi all,
I'm using SQL Server 2005 along side Visual Studio 2005, using VB to create a web application at work.
Now I'd like to bunch together some child fields in a gridview when displayed. Have a look at the attached pic to show what I would like.
Is this possible via a query or would I need to set something up on the gridview to do this?
Cheers,
Paul.
View 9 Replies
View Related
May 8, 2014
In my ETL job I would like to truncate stg table but before truncating stging table, I want to make sure that all the records are inserted in the data model. The sample is as below
create table #stg (
CreateID int,
Name nvarchar(10)
)
insert into #stg
select 1, 'a' union all
select 2, 'b' union all
[Code] ....
How can I check among these tables and make sure that all values are loaded into the data model and the staging table can be truncated.
View 2 Replies
View Related
Oct 24, 2012
I have a column like this:
Table: User
name, field1
Jack 1000|1001|1003
Berg 2000|1001|2004
Paul 1001|1000
Jane 1001
Now, I would like to replace all "1001" with nothing, and also remove the "|"-separator behind 1001 if it exists, basically removing both "1001" and "1001|", so the resulting table looks like this:
name, field1
Jack 1000|1003
Berg 2000|2004
Paul 1000
Jane
My tries, been plenty, but here are some:
Code:
UPDATE UserTable
SET field1 = REPLACE(field1, '1001', '')
UPDATE UserTable
SET field1 = REPLACE(field1, '1001|', '')
But the above queries replaces field1 only if the whole field matches '1001' or '1001|'...
The above queries do work, just like I want them to. I just happened to write them in this order on this post... did not do a copy of the actual query.
View 1 Replies
View Related
Feb 8, 2007
SQL 2k, DDL below.I have a simple table with the following data:fldYear fldCode1 fldCode22000 ABC1 ABC122000 ABC1 ABC132001 ABC1 ABC122002 ABC1 ABC122002 ABC1 ABC13I need to know, for every distinct combination of fldCode1 andfldCode2, if there are any years missing.For example,SELECT DISTINCT fldCode1, fldCode2 FROM MyTablereturnsABC1 ABC12ABC1 ABC13I need to know that in 2001 there was no entry for ABC1/ABC13Thanks!Edwardif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[MyTable]GOCREATE TABLE [dbo].[MyTable] ([fldYear] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldCode1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldCode2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO
View 2 Replies
View Related
Feb 27, 2008
I'm looking for some performance assistance on updating a column value in a table that contains approximately 50 million rows. I have a permanent table in another database that has the key column and value to be set. My query is listed below, but I'm afraid it will run quite awhile. Any suggestions would be appreciated.
update mytable
set column2 = b.column2
from mytable as a
join mytable1 as b
on a.column1 = b.column1
There is a one to one relationship between the two tables.
View 8 Replies
View Related
Dec 5, 2007
Hello, I have a problem. I am trying to pull data out of one system and bring it into a SQL Server database for faster retrieval. The original table does not have an identity column and has a composite primary key. The table I am inserting the data into matches the original table exactly except I have an Identity column that I need for the removal of duplicates. The task gets to the final commit and then fails telling me that it can't insert a NULL into an identity field. Why is it trying to insert a NULL, the field should be auto-populating. I did add the Identity field to the table after the SSIS package was already built, but I did go into the destination and fix the column mappings. For the Identity column I just selected the "skip" option or whatever it was. Why are the identities not being auto-inserted and why is SSIS throwing this error?
Can anyone help???
Thanks!
View 3 Replies
View Related
Dec 24, 2007
Hey Guys
i need to add a datetime column to an exisitng table that has like 1.2 million records and its being accessed frequently
but i cant afford to stop the db at all
whenever i do : alter table mytable add Updated_date datetime
it just takes too long and i have to stop executing the query after a couple of mins
I am running sql express 2005 sp2. db size is over 3 gb but still under the 4 gb limit
can u plz advice on how to add this column. its urgent!!
thanks in advance
View 5 Replies
View Related
Mar 27, 2008
Hi All,
I have following problem
please help me to resolve it.
There is table for shipping
as follows:
City
Kg
Rate(rs)
Rajkot
1
25.00
Rajkot
2
30.00
Rajkot
3
42.50
Ahemedabad
1
42.50
Ahemedabad
2
55.00
Ahemedabad
3
67.50
Ahemedabad
4
80.00
Goa
1
90.00
Goa
2
105.00
Goa
3
120.00
Gondal
1
25.00
Gondal
2
30.00
Gondal
3
42.50
Morbi
1
25.00
Morbi
2
30.00
Morbi
3
42.50
Gandhinagar
1
42.50
Gandhinagar
2
55.00
Gandhinagar
3
67.50
Gandhinagar
4
80.00
We need to display
the table in html page using any Asp.Net language.
The result table should be:
City(s)
1
2
3
4
Rajkot,Gondal,Morbi
25.00
30.00
42.50
-
Ahemedabad,Gandhinagar
42.50
55.00
67.50
80.00
Goa
90.00
105.00
120.00
-
Baruch
70.25
80.00
92.00
120.50
Description:
- Display all the cities
together for which all the shipping charges for (1-4) kgs are same. The range
of kgs is not fixed.
Reply as soon as possible.
Thanks
View 3 Replies
View Related
Jul 16, 2014
Can't seem to make this SQL query work!
Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...
I would like to get the rows where Type is equal to 'TypeA' and Key2 is Null that do NOT have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row
So, given the data
**KEY1** **Key2** **Type**
1 NULL TypeA
2 5 TypeA
3 1 TypeB
4 NULL TypeA
5 NULL TypeB
I would like to return only the row where Key1 = 4 because that row meets the criteria of Type='TypeA'/Key2=NULL and does not have a corresponding row with Type='TypeB'/Key1=Key2 from another row.
I have tried this and it doesn't work...
SELECT t1.Key1, t1.Key2, t1.Type
FROM Table1 t1
WHERE t1.Key2 IS NULL
AND t1.Type LIKE 'TypeA'
AND t1.Key1 NOT IN
(SELECT Key1
FROM Table1 t2
WHERE t1.Key1 = t2.Key2
AND t1.Key1 <> t2.Key1
AND t2.Type LIKE 'TypeB')
View 2 Replies
View Related
May 2, 2015
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2
How can I achieve what I need?
Here are my table definitions and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,
[code].....
View 0 Replies
View Related
Aug 1, 2015
I have a table with following rows.
FY REVCODE Jul Jun
2015 BNQ 1054839 2000000
2015 FNB 89032 1000000
2015 RS 1067299 3000000
I am looking to convert it to
Month BNQ FNB RS
JUL 1054839 89032 1067299
JUN 2000000 1000000 3000000
I tried with the following and result is coming for one month i.e. JUL but not with the second Month i.e Jun
SELECT 'Jul1' AS MON, [BNQ], [FNB], [RS]
FROM
(SELECT REVENUECODE, SUM(ROUND(((Jul/31)*30),0)) AS JUL
FROM RM_USERBUDGETTBL
WHERE USERNAME='rahul' AND FY=2015
GROUP BY REVENUECODE, USERNAME
) AS SourceTable
PIVOT
(SUM(JUL) FOR REVENUECODE IN ([BNQ], [FNB], [RS])) AS PivotTable
Results:
MONTHBNQ FNB RS
Jul11054839 89032 1067299
View 4 Replies
View Related
Sep 3, 2015
I have several databases to deal with, all with + 250 tables. The databases are not identical and do not conform to a specific naming convention for table names. Most but not all tables have a column called "LastUpdated" containing a date/time (obviously). I'd like to be able to find all rows within a whole database (table by table) where the date/time is greater than a specified date/time.
I'm looking for a reliable query that will return all the rows in each of the tables but without me having to write hundreds of individual scripts "SELECT * FROM [dbo.xyz] WHERE LastUpdated > '2015-01-01 09:00:00:000'", or have to look through each table first to determine which of them has the LastUpdated field.
View 9 Replies
View Related
Oct 21, 2013
I have to select rows from a table
if the first 2 characters of a 12 char column are
'GB'
Select BFKEYC from table where
I have a hokey way of doing it but it looks embarrassing:
BFKEYC GT 'GA9999999999'
AND BFKEYC LT 'GC'
View 8 Replies
View Related
Jun 25, 2015
I 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 Related
Jun 14, 2006
Hello, I have a question on sql stored procedures.
I have such a procedure, which returnes me rows with ID-s.
Then in my asp.net page I make from that Id-s a string like
SELECT * FROM [eai.Documents] WHERE CategoryId=11 OR CategoryId=16 OR CategoryId=18.
My question is: Can I do the same in my stored procedure? (without sending it to page)
Here is it:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[eai.GetSubCategoriesById]
(
@Id int
)
AS
declare @path varchar(100);
SELECT @path=Path FROM [eai.FileCategories] WHERE Id = @Id;
SELECT Id, ParentCategoryId, Name, NumActiveAds FROM [eai.FileCategories]
WHERE Path LIKE @Path + '%'
ORDER BY Path
fileCategories table: (for information)Here is the screenshot of the table (10 kb )http://eai.w2o.ru/screen1.gif
Thank you
Artashes
View 12 Replies
View Related
Jul 20, 2005
Hi(SQL Server 2000)I have an existing table (t) with a column that is NOT an identity column(t.ID), but it has manually inserted "row numbers". I want to make thiscolumn become an identity column. This column is a key field to othertables, so I want to keep the row numbers that are allready inserted.From the Query Analyzer, how do I do this?Thanks in advance!Regards,Gunnar V�yenliEDB-konsulent asNORWAY
View 3 Replies
View Related
Apr 24, 2013
IF NOT EXISTS (SELECT TOP 1 1 FROM dbo.syscolumns WHERE id = OBJECT_ID(N'dbo.Employee) and name = 'DoNotCall')
BEGIN
ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit not null Constraint DoNot_Call_Default DEFAULT 0
IF ( @@ERROR <> 0 )
GOTO QuitWithRollback
END
It just takes a LOT of time in SQL Server Management studio. I have to cancel the query and cancelling takes a whole lot time. I am using SQL Server 2008.
View 4 Replies
View Related
Nov 30, 2007
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them.
each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field.
Ideas?
Thanks,
Ad.
View 7 Replies
View Related
Oct 14, 2014
I have 2 different types of data for the same key. My queries renders these results into 2 rows. I'd like them to be one row.
Example
DOG Name_a Color_a Age_a Name_B Color_B Age_B
Boxer BO Fawn 1
Boxer Maggie Brindle 4
Lab Jackson Yellow 2
Lab Sandie Black 3
The result I want
DOG Name_a Color_a Age_a Name_B Color_B Age_B
Boxer BO Fawn 1 Maggie Brindle 4
Lab Jackson Yellow 2 Sandie Black 3
View 1 Replies
View Related
May 18, 2015
I have persons who speaks multiple languages and they are in one table, each row is added if he/she speaks multiple languages. Instead I want to add additional columns and load the data.(what I have shown in the desired output)
name language
-------------
ron english
ron french
ron spanish
andy english
andy hindi
kate english
Desired output
name language1 language2 language3 language4 language5 language6
-----------------------------------------------------------------
ron english french spanish
andy english hindi
Kate english
View 4 Replies
View Related
Nov 16, 2015
Given a table that has three columns that together create a key and two columns that together define NameValue pairs, how can the difference between instances of values be calculated and displayed?One table is used to contain periodic dumps of data from various sources. Because this is an early stage of development for the software project instead of having explicit columns that contain specific data the table contains name/value pairs. This allows the software to export anything to the database table. When this data is imported, earch row shares the same key (three columns containing a machine type, serial number and a timestamp), a name that identifies the data and a string that contains the actual data. While this arrangement makes it trivial to support the addition of any data that the software developers want to export, it makes it less obvious as to how to generate reports.Let's make an example. Lets assume that there are two vending machines, each of which has just 3 snacks and each of which generates two separate reports.
Type Sn Timestamp Name Value
A 1 2015-08-15 12:34 Snick 5
A 1 2015-08-15 12:34 Mars 10
A 1 2015-08-15 12:34 MandM 0B 2 2015-08-15 15:31 Snick 1
B 2 2015-08-15 15:31 Mars 9
B 2 2015-08-15 15:31 MandM 0A 1 2015-08-21 09:12 Snick 11
A 1 2015-08-21 09:12 Mars 18
[code]...
So, the names of the values become the report's columns. The reports are sorted by timestamp, then by type, then by serial number.The value associated with the previous row that shares the same name is subtracted from the value of the next row in which the same name occurs and that becomes the displayed value in the report.
View 8 Replies
View Related
Mar 28, 2012
how do you draw an E-R diagram from a bunch of functional dependencies?
I mean, where do you start? Can I just pick, say, E to be the starting point and have branches coming out to all the other attributes?
View 6 Replies
View Related
Aug 27, 2007
Hi All,
Not sure if this is exactly the place to post this, but here it goes anyways.
I am writing a ASP.Net/C# program and I am interacting with a MS Access database in order to derive data on user login and logout times. Basically, I am trying to create a line graph that will display the number of users over the course of a user specified timespan. Currently, I am doing this by look at the number of users that were logged on during each minute of the timespan.
My database table setup consists of a EmployeeID column (Text), Logon Date (Date/Time), and Logoff Date (Date/Time). I have also created an index on the Logon Date and Logoff Date columns.
In order to view the number of users during a minute of the timespan I use a Jet SQL query of the following format
Code Snippet
SELECT DISTINCT Count (EmployeeID) AS [User Count]
FROM ProgramName
WHERE ([Logon Date] < #August 27, 2007 11:45:00# OR [Logon Date] Is Null) AND ([Logoff Date] > #August 27, 2007 11:45:00# OR [Logoff Date] Is Null)
The problem is that using this method I have to execute 1,440 queries for each day in the timespan. Currently this takes about 25 seconds to execute if the timespan is a full workweek (7,200 queries).
Now the question. Is it possible to create a SELECT statement that will return user counts for multiple minutes? Like maybe a SELECT statement that returns a column of counts for every minute in an hour? If it is possible, does anyone have any examples? I am hoping by lowering the number of queries my program has to execute I will also cut down the time required for the code to run.
I am pretty new to SQL, so any guidance or advice is very appreciated.
Thanks!
View 4 Replies
View Related
Sep 8, 2006
i am getting the above error on my database i have 2 rows with the same info on and another 2 with the same info on. example:
ID username password
1 bob bob
1 bob bob
1 john john
1 john john
I know this is a fault with ms sql 2005 however how do i fix it?
Ive found this link which explains everything but how do i start a query. I tried clicking on new query and copying the code. What is table1 meant to be? the database is dbl.tbl_admin. It wont find my database.
Im not sure how to do it anyway.
I need to change it though as its my admin password and Ive given it out to web design companys
http://geekswithblogs.net/allensb/archive/2006/07/27/86484.aspx
Can some 1 read the above page and give me full instructions, I dont know what im doing thanks
info@uktattoostudios.co.uk
View 7 Replies
View Related
Mar 10, 2015
See pic
Syntax on using BCP.
Here is my Requirements: I have a file that has a bunch of INSERT STATEMENTS. So the stuff inside the file looks like the following:
File has about 5000 rows.
INSERT INTO abc ( name ) VALUES ( 'Peter' );
INSERT INTO abc ( name ) VALUES ( 'Bob' );
View 2 Replies
View Related
Dec 18, 2007
I found in error log of my server is full of message like this
The query notification dialog on conversation handle '{9AD14600-28AD-DC11-9B36-000D5670268E}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service 'SqlQueryNotificationService-174a4df0-bac8-4a51-8564-28d750d7c11a'.</Description></Error>'.
any idea what could cause that?
thanks!
View 2 Replies
View Related
Mar 29, 2007
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
View 1 Replies
View Related
Jul 8, 2004
I have a table that contains names that are all in upper case, this column is called in many different areas of my web app. I wanted to make the names all lowercase, or with the leading character only capitalized.
How can I make a column within a SQL table lowercase at the SQL server end and not the programming side?
thanks,
Frank
View 3 Replies
View Related
Nov 29, 2006
Here is my create table command
CREATE TABLE EXCLUSIVEITEM(VIEW_ID INTEGER NONCLUSTERED IDENTITY(1,1)PRIMARY KEY,
VIEW_LATEST_REPLY_DATE CLUSTERED INDEX DATETIME)
Is this the way to create one of my columns clustered. The books that I have all use stored procedure I only want to use regular sql commands. Is this possible?
View 1 Replies
View Related
Aug 4, 2007
Hai Everyone,
I have a table with data like this
Tran_no Description Debit Credit
1 SW 1000
1 BR1 500
1 BR2 200
1 BR3 300
2 SQ 500
2 BW1 200
2 BW2 200
2 BW3 100
Now what I wanna do this that
Tran_no Val1 Val2 Val3 Val4
1 1000 500 200 300
2 500 200 200 100
Thanks in advance
Nirene
View 10 Replies
View Related
