Adding a row to a column set

Question

Adding a row to a column set

Viewing 14 posts - 16 through 28 (of 28 total)

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

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 1

looks like something else i need to read up on more,
cheers

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know

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

Carlo Romagnano - Tuesday, September 11, 2018 2:55 AM
Giles Sutcliffe - Tuesday, September 11, 2018 2:20 AM
Queries as written return no data whatsoever, so none of the answers is correct, surely?
You aren't correct! The query returns an empty resultset with the columns and types defined in the first answer.

On the contrary, Giles said is returns no data which is correct. It is an empty result set which means no data was returned. 😉

_______________________________________________________________

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/

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

edwardwill - Tuesday, September 11, 2018 1:02 AM
Stewart "Arturius" Campbell - Tuesday, September 11, 2018 12:43 AM
ildjarn.is.dead - Tuesday, September 11, 2018 12:37 AM
Shouldn't the query read 'WHERE uc.UserID = 8'?
That thought did occur to me as well.
It is still a good question, despite that.....
How can it be a good question if it's a terrible question?

I would argue that it isn't a terrible question. It does have a typo but since none of the answers were that it returns no rows it wasn't a huge leap to assume Steve meant UserID instead of UserConfigKey. Or since there was only a single row in the table no where clause is even needed to demonstrate what he was getting at.

_______________________________________________________________

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/

edwardwill SSCertifiable Points: 5851 More actions · Answer 4

Sean Lange - Tuesday, September 11, 2018 7:08 AM
edwardwill - Tuesday, September 11, 2018 1:02 AM
Stewart "Arturius" Campbell - Tuesday, September 11, 2018 12:43 AM
ildjarn.is.dead - Tuesday, September 11, 2018 12:37 AM
Shouldn't the query read 'WHERE uc.UserID = 8'?
That thought did occur to me as well.
It is still a good question, despite that.....
How can it be a good question if it's a terrible question?
I would argue that it isn't a terrible question. It does have a typo but since none of the answers were that it returns no rows it wasn't a huge leap to assume Steve meant UserID instead of UserConfigKey. Or since there was only a single row in the table no where clause is even needed to demonstrate what he was getting at.

It might have been a good question, had the question that you have assumed was intended been asked. And the correct answer given.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

edwardwill - Tuesday, September 11, 2018 7:10 AM
It might have been a good question, had the question that you have assumed was intended been asked. And the correct answer given.

The correct answer was given. If you simply either remove the where clause entirely or change it to UserID = 8 then correct answer is exactly what is returned.

_______________________________________________________________

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/

mike.sortino Mr or Mrs. 500 Points: 544 More actions · Answer 6

edwardwill - Tuesday, September 11, 2018 2:48 AM
Giles Sutcliffe - Tuesday, September 11, 2018 2:20 AM
Queries as written return no data whatsoever, so none of the answers is correct, surely?
That's the option I was looking for.

Me too, and it would not be the first time Steve tricked us like that. However, I still do not see the difference between option 1 and option 2.

edwardwill SSCertifiable Points: 5851 More actions · Answer 7

Sean Lange - Tuesday, September 11, 2018 7:16 AM
edwardwill - Tuesday, September 11, 2018 7:10 AM
It might have been a good question, had the question that you have assumed was intended been asked. And the correct answer given.
The correct answer was given. If you simply either remove the where clause entirely or change it to UserID = 8 then correct answer is exactly what is returned.

I realise it's only a bit of fun and there's no jeopardy, but the relaxed attitude towards correctness hereabouts is slightly alarming.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

edwardwill - Tuesday, September 11, 2018 7:21 AM
Sean Lange - Tuesday, September 11, 2018 7:16 AM
edwardwill - Tuesday, September 11, 2018 7:10 AM
It might have been a good question, had the question that you have assumed was intended been asked. And the correct answer given.
The correct answer was given. If you simply either remove the where clause entirely or change it to UserID = 8 then correct answer is exactly what is returned.
I realise it's only a bit of fun and there's no jeopardy, but the relaxed attitude towards correctness hereabouts is slightly alarming.

Understand what you mean. In any other spot around this site you will find there is no such thing as being relaxed about correctness. But the QOTD is very laid back and often a bit off. 😀

_______________________________________________________________

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/

below86 SSChampion Points: 11532 More actions · Answer 9

As others have said running the SQL as written returns nothing, so option one is wrong. This part makes it wrong "and the Options column with 3 XML nodes."
Why don't we put these questions out as temp tables instead of permanent tables?
CREATE TABLE #UserConfig ( UserConfigKey INT IDENTITY(1,1) NOT NULL CONSTRAINT UserConfigPK PRIMARY KEY , UserID INT , IsActive BIT SPARSE , IsSubscriber BIT SPARSE , DefaultQuantity INT SPARSE , Options XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ) ; --We run this query

INSERT #UserConfig ( UserID , IsActive , IsSubscriber , DefaultQuantity ) VALUES (8, 1, 1, 5) ; --and then this

SELECT * FROM #UserConfig AS uc WHERE uc.UserConfigKey = 8 ;

SELECT * FROM #UserConfig AS uc WHERE uc.UserID = 8 ;
Results as the question is written is the first result, I just wouldn't have assumed Steve meant to use USERID. If that was the case, why even include the .UserConfigKey column?

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Yasir Anis SSCommitted Points: 1861 More actions · Answer 10

edwardwill - Tuesday, September 11, 2018 2:48 AM
Giles Sutcliffe - Tuesday, September 11, 2018 2:20 AM
Queries as written return no data whatsoever, so none of the answers is correct, surely?
That's the option I was looking for.

Same here 🙂

webrunner SSC-Dedicated Points: 31862 More actions · Answer 11

ildjarn.is.dead - Tuesday, September 11, 2018 12:37 AM
Shouldn't the query read 'WHERE uc.UserID = 8'?

Yup seems like it. This returned the rows for me.

SELECT
*
FROM dbo.UserConfig AS uc
WHERE uc.UserID = 8
;

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 12

Giles Sutcliffe - Tuesday, September 11, 2018 2:20 AM
Queries as written return no data whatsoever, so none of the answers is correct, surely?

That's not necessarily true, but there is a typo there with the implication that there's only one row.

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 13

mike.sortino - Tuesday, September 11, 2018 7:20 AM
Me too, and it would not be the first time Steve tricked us like that. However, I still do not see the difference between option 1 and option 2.

The sparse columns are not returned. The result set has 3 columns, not 6

brad.mason5 Hall of Fame Points: 3299 More actions · Answer 14

I like this question. If you include XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
This will not include the actual columns that have SPARSE as the type definition it seems.

But if you remove the XML COLUMN_SET FOR ALL_SPARSE_COLUMNS. You get all the columns returned.

This could be a way of Steve saying here is another reason to not use select *