[T-SQL] Passing Multiple Values As Parameters to get Multiple Columns with Comma Seperation(MSSQL)

---

• Passing Multiple Values As Parameters to get Multiple Columns with Comma Seperation(MSSQL)
• Query help needed - Like a Pivot with generic headings?
• Urgent Query help needed- calculate one field based on other field in SELECT statment

Passing Multiple Values As Parameters to get Multiple Columns with Comma Seperation(MSSQL) Posted: 04 Apr 2013 10:06 PM PDT Create procedure temp(@MID smallint)asBeginselect TranID,[MonthValue]=(CASE WHEN @MID=1 THEN Jan WHEN @MID=2 THEN Feb WHEN @MID=3 THEN Mar WHEN @MID=4 THEN Apr WHEN @MID=5 THEN May WHEN @MID=6 THEN Jun WHEN @MID=7 THEN Jul END) FROM TblTran as M where TranID=1 and M.Month = @MIDendThis is a stored procedure with a parameter @MID that i'm using to generate a report using SSRS.If a single value is passed to the parameter it works fine.For example-Transaction Table[b][u]TranID | Apr | May | Jun | Jul [/u][/b] 1 | 50 | 30 | 11 | 30 2 | 51 | 39 | 100 | 30if i execute with[b]Exec 4[/b]the result is what i expect[b][u]TranID | MonthValue [/u] [/b] 1 | 50 **-- ie Aprils value**But I need to pass multiple values to the parameterlike[b]exec 4,5,6[/b]and desired result should be[u][b]TranID | MonthValue [/b] [/u] 1 | 50,30,11 ***-->Comma Separated values of columns how can i acheive result like this??

Query help needed - Like a Pivot with generic headings? Posted: 13 Apr 2013 06:45 AM PDT [code="sql"]CREATE TABLE [dbo].[mike_inventory]( [itemid] [int] NULL, [location] [varchar](50) NULL[/code][code="sql"]insert into mike_inventory values (10131,'Row A')insert into mike_inventory values (10131,'Row B')insert into mike_inventory values (10132,'Row B')insert into mike_inventory values (10132,'Row C')insert into mike_inventory values (10133,'Row A')insert into mike_inventory values (10133,'Row D')insert into mike_inventory values (10134,'Row A')insert into mike_inventory values (10134,'Row E')[/code]I want my output to look like this[code="sql"]Itemid Location1 Location2 Location3--------------------------------------------------------------------------------------------10131 ROW A ROW B 10132 ROW B ROW C 10133 ROW A ROW D10134 ROW A ROW E[/code]Is this possible?

Urgent Query help needed- calculate one field based on other field in SELECT statment Posted: 13 Apr 2013 08:55 AM PDT Hi Friends,I have reporting TSQL query- in this query within the select statement I have 2 fields 1) Age (working Days) and 2) Age Group(Working days).the requirement demands that based on the first Select field - Age(working Days) I, display one of these selections: 0 - 2930 - 5960 - 8990 - 119120 +for the Second SELECT list field Age Group(Working Days)Note- the data Type for both is Varchar.thanksDhananjay

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server Data Warehousing] T-SQL SCD2

Here is the query i am using in case if it helps.

To give you little more scenario  on my request  : Our Data warehouse is very traditional approach of ETL , We have update statments that does lookup and update the Foriegn keys.

My first update statments goes and finds an employee based on a logic of Data Entry

Update dbo.SFWP 
Set EmployeeID  = we2.EmployeeSK
from dbo.SFWP a
Inner Join WMDM.dbo.WMDM AT
on  a.AssignedTaskID = AT.AssignedTaskID
Inner join WMDM.dbo.Employee we1
on AT.EmployeeID = we1.SK
Inner Join WMDM.dbo.Employee we2
on we1.NK = we2.NK
and  Src_EntryDate >= we2.EffectiveBeginDTM and 
Src_EntryDate <= we2.EffectiveEndDTM

Apparently for some reason some employees cannot be matched because  our employee table has this condition not met because of missing records

and  Src_EntryDate >= we2.EffectiveBeginDTM and
Src_EntryDate <= we2.EffectiveEndDTM

So, my new query i am trying to take a previous or Next record and find employee ID and update it.

wITH CustCTE as
(
select  
We2.SK,
We2.NK,
We2.EffectiveBeginDTM,
We2.EffectiveEndDTM,
ROW_NUMBER() OVER (PARTITION BY We2.NK ORDER BY We2.EffectiveBeginDTM) RowNum
from dbo.SFWP a
Inner Join WMDM.dbo.WMDM AT
on  a.AssignedTaskID = AT.AssignedTaskID
Inner join WMDM.dbo.Employee we1
on AT.EmployeeID = we1.SK
Inner Join WMDM.dbo.Employee we2
on we1.NK = we2.NK
Where 
 Src_EntryDate < we2.EffectiveBeginDTM or 
Src_EntryDate > we2.EffectiveEndDTM
and a.EmployeeID is NULL
)
,
CustCTE1 as (
Select 
a.SK
,a.NK
,a.EffectiveBeginDTM
,a.EffectiveEndDTM
,a.RowNum
    
From CustCTE a
Union All 
Select 
Curr.SK
,Curr.NK
,Curr.EffectiveBeginDTM
,Curr.EffectiveEndDTM
,Curr.RowNum
    
From CustCTE Curr
Inner Join      CustCTE1 Prev
on Curr.NK = Prev.NK
and Curr.RowNum = Prev.RowNum-1
)
select * From CustCTE1

But i am not getting the same thing as i expect..

Any help in this matter will be highly appreciated.

thank you in advance

.

social.technet.microsoft.com/Forums

[SQL Server 2008 issues] DBCC CHECKDB Failing

---

• DBCC CHECKDB Failing
• SQL server:warning when editing or changing schema.
• Index on a view
• Detect Database is being recovered condition.
• sql:space at the end of string

DBCC CHECKDB Failing Posted: 13 Apr 2013 07:01 PM PDT Hi,We are running DBCC CHECKDB WITH ALL_ERRORMSGS.After couple of minutes, the execution fails, sql service crashes and we get the following error:Msg 64, Level 20, State 0, Line 0A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)We then retry executing the same command again. In the second attempt the command executes successfully.Why is it failing on the first attempt?

SQL server:warning when editing or changing schema. Posted: 12 Apr 2013 11:22 PM PDT In our production sql server we have so many important databases. I have created two login 'admin'(has all permission) and 'read'(has only public and datareader permission) . I have done this only because when i want to see or read the database i use login 'read' so that there is no chance of any mistakes happening like deletion,rename etc when i access in SSMS.(Sometimes it happens i by mistake press f2 button and then some button and table name gets renamed).So i use the 'read' login.When i required to change table columns etc then i use 'admin' login. So i use 'admin; login only when i required and otherwise i use 'read' login only for protecting data.So do i have any option by which i can use my 'admin' login always , but whenever any schema(like column rename,delete) changes i want a alert so that i can came to know about it. Is there any option for doing it?

Index on a view Posted: 13 Apr 2013 09:46 AM PDT Hi,DTA keeps recommending creating views and then building indexes on them. On a few high cost queries, the recommendation will be a 99% performance gain so I really want to do it but in the past I have done it twice and both times it caused an incident when it was time to do inserts on the table with an indexed view. This error has put me in a lot of hot water so I am afraid to make it again."INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations."Are indexed views as dangerous as they seem?Thanks for reading.Howard

Detect Database is being recovered condition. Posted: 13 Apr 2013 12:56 AM PDT I have "Live" databases and log shipped databases co-mingled on the same server.The log ship state that the log shipped databases are in is normally STAND BY unless they are actively being refreshed with the latest logs.I am using the following TSQL.IF EXISTS ( SELECT * FROM sys.databases d WHERE d.is_in_standby=0 AND d.is_read_only=0 AND d.state=0 AND d.name = 'MyDB' AND d.state_desc='ONLINE' )BEGIN Exec ('USE MyDB:Run some sql')END Based on this I would expect that I could bypass all my databases that are being log shipped or are in standby or recovery mode.However, using the above TSQL I still intermittently receive the following error:Database 'MyDB' is being recovered. Waiting until recovery is finished.Is there another way to detect this condition for a database?This is a SQL 2k8 database.Thanks in advance

sql:space at the end of string Posted: 12 Apr 2013 07:29 PM PDT I have a sql code like shown belowdeclare @str nvarchar(max),@i intset @i=0set @str='abc 'declare @tbl Table(a nvarchar(max)) insert @tbl select @strwhile (select a from @tbl)<>''begin set @i=@i+1 set @str = substring(@str,2,len(@str)) update @tbl set a=@strselect * from @tblendHere @str has value 'abc ' (there is an space at the end) when above query is executed it will stop when only a space is present in 'a'.Also output of this query is:bcc<here blank>for the above query if i give input @str as 'abcd'then output will bebcdcdd<here blank>So in the first case that is @str='abc ' i want to get output likebcc<here blank><here blank>Now the code is checking for space and because of that i am having problem. But i want it to consider the space at the end also.Same is the problem in sql for len also.Both len('a') and len('a ') will return 1.So if anyone please help on my query so that it will give my desired output.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server] Advanced SQL Server 2008 Extended Events with Examples

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

.

sqlteam.com

[SQL server issues] Created Issue: New to datawarehousing...and want to know how the Adventurework and AdventureworkDW has been developed [17881]

description

New to datawarehousing...and want to know how the Adventurework and AdventureworkDW has been developed..
how the OLTP data has been extracted to AdventureworksDW..datawarehouse system and need some design documents....what to know how the DIM tables are created...and everything

Sign in to add a comment or to set email notifications

.

sqlserversamples.codeplex.com

[SQL Server] Testing with Profiler Custom Events and Database Snapshots

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

.

sqlteam.com

[SQL Server Data Warehousing] Need some clarifications on Sql Server 2012 QA & Prod environment setup

I think the original question requested information on Licensing as well, which none of the answers contain. I agree on the HW configuration part - they majorly depend on application size including data volume.

With SQL Server 2012 we can use Standard (Core / Server-CAL), BI (Server-CAL) and Enterprise (Core) editions, - among them which edition to choose will depend on what features the solution needs. On high level, if the solution needs canned reporting on moderate sized data, Standard edition will do. If it requires ad-hoc / self-service BI then we need BI or Enterprise edition. If the data volume is high requiring columnstore indexing, we need Enterprise edition.

So far so good, all these are options for the Prod environment.

But for Dev and QA, can we use Developer edition license:

1. Will that license be able to access data from some other SQL Server database (which might be copy of Prod data for Dev and QA)

2. Will that license enable us to move SSIS/RS/AS/PowerView codes to Prod environment without any issues? 

Can some expert from Microsoft answer this question - which I think would answer a good part of the original question as well.

---

Souvik Banerjee. MSBI Lead, Zensar Technologies

.

social.technet.microsoft.com/Forums