Expressions based on data in the SELECT statement

Question

Post reply

Expressions based on data in the SELECT statement

Viewing 5 posts - 16 through 19 (of 19 total)

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

jeanangeo SSC Enthusiast Points: 157 More actions · Answer 1

Hi again,

Thanks to the help I've been given so far I am nearly there. I have the data I need in one table which I've recreated in the ddl below.

There's one other thing I need to know how to do. I need to be able to convert from the table format in the ddl:



--===== Check for existing tables

IF OBJECT_ID('TempDB..#Results','U') IS NOT NULL

	DROP TABLE #Results

--===== Create the Test table

CREATE TABLE #Results

(

	SessionId  INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

	SessionName nvarchar(16) NOT NULL,

	SessionType NVARCHAR(16) NOT NULL,

	Quota1 bit NOT NULL,

	Quota2 bit NOT NULL,

	Quota3 bit NOT NULL,

	SeenFirst bit NOT NULL,

	SeenInTime bit NOT NULL,

	SeenAtAll bit NOT NULL,

)

SET IDENTITY_INSERT #Results ON

--===== Sessions test data

INSERT INTO #Results

(SessionId, SessionName, SessionType, Quota1, Quota2, Quota3, SeenFirst, SeenInTime, SeenAtAll)

SELECT '1', 'Session 1', 'Control', '0', '1', '0', '0', '1', '1' UNION ALL

SELECT '2', 'Session 2', 'Test', '0', '1', '1', '1', '0', '1' UNION ALL

SELECT '3', 'Session 3', 'Test', '1', '1', '0', '1', '1', '1' UNION ALL

SELECT '4', 'Session 4', 'Control', '0', '0', '1', '0', '0', '0' UNION ALL

SELECT '5', 'Session 5', 'Control', '0', '1', '0', '0', '0', '1' UNION ALL

SELECT '6', 'Session 6', 'Control', '1', '1', '1', '0', '1', '1' UNION ALL

SELECT '7', 'Session 7', 'Test', '0', '1', '1', '1', '0', '1' UNION ALL

SELECT '8', 'Session 8', 'Control', '1', '0', '1', '1', '1', '1' UNION ALL

SELECT '9', 'Session 9', 'Test', '0', '1', '0', '1', '0', '1' UNION ALL

SELECT '10', 'Session 10', 'Test', '1', '1', '1', '0', '0', '1' UNION ALL

SELECT '11', 'Session 11', 'Test', '0', '1', '1', '0', '0', '0' UNION ALL

SELECT '12', 'Session 12', 'Control', '1', '1', '0', '0', '0', '1' UNION ALL

SELECT '13', 'Session 13', 'Test', '1', '0', '1', '1', '0', '1' UNION ALL

SELECT '14', 'Session 14', 'Control', '1', '1', '0', '0', '1', '1' UNION ALL

SELECT '15', 'Session 15', 'Control', '0', '1', '0', '0', '0', '1'

SET IDENTITY_INSERT #Results OFF

SELECT * FROM #Results

.......to

Session Quota SeenFirst SeenInTime SeenAtAll

1 Q2 0 1 1

2 Q2 1 0 1

3 Q3 1 0 1

I think this is either cross tabs or pivots and I am currently looking at Jeff Morden's article. My confusion is that I may be wrong in the first place and also in my scenario, a session could be flagged for all of the quotas and therefore I need one row per quota.

Could someone point me in the right direction?

Thanks again

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Sorry... this got seriously lost in the wood pile... are you all set on this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

jeanangeo-558795 SSC Enthusiast Points: 131 More actions · Answer 3

Hi Jeff,

Yes thanks that project is now completed. The last question I posted was merely a case of having stared at the problem too long and not being able to see the wood for the trees. I figured it out a few hours later after a break.

It was the most complicated SQL stuff I've done. My SQL experience has been limited over the last few years due to working for a company that uses an object relational mapping tool rather than stored procedures. We don't get into heavy duty SQL neraly often enough and I wasn't that good in the first place. I'm fairly sure I will look back at it in a few years and see a million areas where I could improve the performance but it's out the door and has survived load tetsing so far.

I've learnt a lot and this site was a tremendous help. It's firmly on my list of favourites and am attempting to read around as much as I can. There is just sooo much to read lol.

Anyway, thanks again to all who leant me a hand and to you Jeff for your double chek of the status.

Kind regards

jeanangeo

--------------------------------
I found this article helpful in passing on the info the more experienced posters need to help me with my problem:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Thanks for the feedback, Jeanangeo. Glad you got it solved.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Garadin One Orange Chip Points: 29613 More actions · Answer 5

jeanangeo (12/27/2008)

I'm fairly sure I will look back at it in a few years and see a million areas where I could improve the performance but it's out the door and has survived load tetsing so far.

It's truly amazing how much you can learn from this site if you spend some time here. I look back on things I wrote a couple *months* ago and shake my head in disbelief. I'm sure I'll do the same in another couple months at the code I write now.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]