QUERY HELP PLEASE

Question

QUERY HELP PLEASE

Smash125

Hall of Fame

Points: 3562
More actions
November 18, 2014 at 3:09 am

#292569

i have requirement this
CREATE TABLE [dbo].[TEST_REP_MGR_NAME](
[ACCT_ID] [decimal](15, 0) NULL,
[ACCT_NAME] [nvarchar](max) NULL,
[COUNTRY] [nvarchar](max) NULL,
[SEGMENT] [nvarchar](max) NULL,
[FISC_QTR_VAL] [nvarchar](max) NULL,
[FISC_WEEK_VAL] [nvarchar](max) NULL,
[CLDR_DATE] [smalldatetime] NULL,
[ITM_NBR] [nvarchar](max) NULL,
[ACCOUNT_TYPE] [nvarchar](max) NULL,
[ITM_TYPE] [nvarchar](max) NULL,
[ORDER_STATUS] [nvarchar](max) NULL,
[PAYMENT] [smallint] NULL,
[REP_NAME1] [nvarchar](max) NULL,
[REP_ID_NUM1] [nvarchar](max) NULL,
[MGR_NAME1] [nvarchar](max) NULL,
[MGR_ID_NUM1] [nvarchar](max) NULL,
[REP_NAME2] [nvarchar](max) NULL,
[REP_BA_NUM2] [nvarchar](max) NULL,
[MGR_NAME2] [nvarchar](max) NULL,
[MGR_ID_NUM2] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO [dbo].[TEST_REP_MGR_NAME]
VALUES
('597545825','CORPORATION','US','GLOBAL','2015-Q4','2015-W40','2014-11-05 00:00:00','341-9096','STR','OEM','Invoiced','10','Minor, Bryan','435050','Hutcherson, Jeff','9911','Minor, Bryan','435050','Urrutia, Heinar','597052')
GO
INSERT INTO [dbo].[TEST_REP_MGR_NAME]
VALUES
('597545825','CORPORATION','US','GLOBAL','2015-Q4','2015-W40','2014-11-05 00:00:00','341-9096','STR','OEM','Invoiced','10','Minor, Bryan','435050','Hutcherson, Jeff','9911','Minor, Bryan','435050','Urrutia, Heinar','597052')
INSERT INTO [dbo].[TEST_REP_MGR_NAME]
VALUES
('727390524','CORPORATION','US','GLOBAL','2015-Q4','2015-W40','2014-11-05 00:00:00','341-9096','STR','OEM','Invoiced','10','Minor, Bryan1','435050','Hutcherson, Jeff1','9911','Minor, Bryan1','435050','Urrutia, Heinar1','597052')
in this query i have rep_name1 and rep_name2 and MGR_NAME1 and MGR_NAME2. I am developing SSRS report. My requirement is combine rep_name1 and rep_name2 and mgr_name1 and mgr_name2. So that i have two filters at the top as rep_name and mgr name. Table structure is going to remain same when i display report. please let me how to go about it

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Are rep_name1 and rep_name2 two different names? Or are they the same name divided into 2 columns? The solution might vary from one option to another. I hope it's the same answer for mgr_names.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

I would recommend taking a long look at your structure. Everything is varchar(max). Do you really need that many characters in every column? There is no primary and since every single column is nullable you can't even create one from the columns listed. Then you have what appears to be denormalized sets of data.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/