Tuesday, June 18, 2013

[SQL Server] Creating Table for Pivot Table

[SQL Server] Creating Table for Pivot Table

Creating Table for Pivot Table

Posted: 18 Jun 2013 03:33 AM PDT

Hello,I need to create a pivot table, and I think I need a new column to get the pivot to work. Basically, what I have is data with customer#, customername and dist. Each customer can have more than 1 distributor, and I want my data to be arranged like: customer#, customername, dist1, dist2, dist3, etc (as many as they have).I think I need to add a number column that will count each dist for each customer. But how do I do this?Thanks!Mary

Separating comma seperated values

Posted: 18 Jun 2013 08:06 AM PDT

I would like to pass a column to a function, and have it unioned together with my other data in it's own row... for example given the data:CommaColumn ValueColumn1 ValueColumn2----------- ------------ -------------ABC,123 1 2XYZ, 789 2 3I would like to write something like:SELECT Split(CommaColumn,',') As SplitValue, ValueColumn1, ValueColumn2 FROM MyTableAnd get backSplitValue ValueColumn1 ValueColumn2---------- ------------ ------------ABC 1 2123 1 2XYZ 2 3789 2 3Is this possible, or has anyone done this before?

having trouble granting role to user

Posted: 18 Jun 2013 07:17 AM PDT

I've submitted these SQL commands with no problem:CREATE ROLE testing; -- create Role "testing"GRANT CREATE TABLE TO testing; -- grant CREATE TABLE privilege to ROLE "testing"Now I want to grant role "testing" to user "test_user".But when I try GRANT testing TO test_user;or GRANT ROLE testing TO test_user;I get Incorrect syntax near 'testing'.When I try EXEC sp_addrolemember testing, test_user;I get User or role 'test_user' does not exist in this database.even though I HAD already created a new login for test_user in Object Explorer | < database engine > | Security | Logins (right-click)I did a search on SQL how to GRANT ROLE to userbut couldn't find an answer that worked for me. Someone even suggested GRANT testing ON test_user TO AdventureWorksDW;but again I got Incorrect syntax near 'testing'.Incidentally, just to be sure, I tried all this using an Administrator level Windows user account and an Administrator level SQL Server account.Also incidentally, I'm running SQL Server 2008 with Advanced Services under Windows XP Pro.Any suggestions? Thanks for any help anyone can give.

Trying to join 3 tables from different servers

Posted: 18 Jun 2013 02:23 AM PDT

I have been trying to join 3 different tables from 2 different servers and I am having some success but have reached a wall.Query (figured I show the actual)Select * FROM [VisNetic Mailflow].[dbo].[Tickets] [t] left join [VisNetic Mailflow].[dbo].[TicketHistory] [th] on [t].[ticketid] = [th].[ticketid] left join [VisNetic Mailflow].[dbo].[Agents] [a] on [th].[AgentID] = [a].[AgentID] left join [VisNetic Mailflow].[dbo].[TicketBoxes] [tb] on [t].[TicketBoxID] = [tb].[TicketBoxID] left join [VisNetic Mailflow].[dbo].[TicketStates] [ts] on [t].[TicketStateID] = [ts].[TicketStateID] left join [VisNetic Mailflow].[dbo].[TicketActions] [ta] on [th].[TicketActionID] = [ta].[TicketActionID] left join [VisNetic Mailflow].[dbo].[AgentGroupings] [ag] on [th].[AgentID] = [ag].[AgentID] left join [VisNetic Mailflow].[dbo].[Groups] [g]on [g].[GroupID] = [ag].[GroupID] left join [VisNetic Mailflow].[dbo].[OutboundMessages] [om] on [t].[TicketID] = [om].[TicketID] left join [PDO_Live].[dbo].[ACCOUNT] [ac] on [t].[Contacts] = [ac].[STR_EMAIL_ADDRESS] left join [PDO_Live].[dbo].[vwAPPLICATION_SUMMARY] [vwapp] on [t].[Contacts] = [vwapp].[STR_EMAIL_ADDRESS] left join [I3_IC].[dbo].[CallDetail] [cd] on [t].[Contacts] = [cd].[RemoteNumberFmt] where [t].[DateCreated] between '05-01-2013' and '05-02-2013' and [th].[TicketBoxID] = '16' Order by [t].[DateCreated]and then take and join with [Server2].[db3].[arrival].**The end result would be to show where the unique incident lead to the 1st call(HH:mm:ss) after the incident took place.when I execute the query I get the error *** Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation. ***

how to copy for a file in sub directories basing on the file name in ssis

Posted: 18 Jun 2013 02:57 AM PDT

How to copy a particular file from sub folders basing on the file name. I have multiple sub folders and the file which i am looking for may present in the sub folder level or it may be present in another sub folders under the present sub folder. can any of you guys help me in this

Move a 2008 database to a new machine on 2008R2

Posted: 17 Jun 2013 09:45 PM PDT

I was supposed to be migrating some 2008 (SP3) databases from old physical machines to new virtual machines. I was just going to do a straightforward backup, move file, restore, etc. etc.At the last minute I've been told the new machines need to be running 2008R2, not 2008. As is usual where I work it's all seat of the pants stuff, and has to be done NOW. Help! What's the sequence of events here? (I've been Googling but I'm getting a lot of answers about how to restore an R2 database to 2008, but not the other way around.) I can't upgrade the current databases to R2, only the destination ones, which is what is complicating things. I'm sorry if this is really simple; I've been ill for a week with a raging temperature and I can't think straight. Any help would be gratefully received.
Posted by at

1 comment:

Subscribe to: Post Comments (Atom)

Search This Blog