Tag Archives: sql

Hierarchical table sorting with a parent-child relationship

Posted on by

This code snippet works beautifully. Just replace the table/field names like so: 

  • AccountID with ID of your child record
  • name with name of your child record
  • parentID with the parent ID of of your child record
  • @tbl_accounts with the name of your hierarchical table

Run and done:

with cte as
(
select
    Accountid,
    name,
    parentid,
    cast(row_number()over(partition by parentid order by name) as varchar(max)) as [path]
from @tbl_accounts
where parentid = 0
union all
select
    t.AccountID,
    t.name,
    t.ParentID,
    [path] + cast(row_number()over(partition by t.parentid order by t.name) as varchar(max))
from
30.    cte
join @tbl_accounts t on cte.AccountID = t.ParentID
)
 
select 
    Accountid,
    name,
    ParentID,
    [path]
from cte
order by path

 

Thanks, Kev Riley!

http://ask.sqlservercentral.com/questions/48518/hierarchical-table-sorting-with-a-parent-child-rel.html

Prevent saving changes that require table re-creation

Posted on by

I love this little tip because I prefer to edit a sql db table through the GUI if possible. (I know, I know. Leave me alone.)

Pedro’s answer is short and sweet:

Go into Tools -> Options -> Designers-> Uncheck “Prevent saving changes that require table re-creation”. Voila.

That happens because sometimes it is necessary to drop and recreate a table in order to change something. This can take a while, since all data must be copied to a temp table and then re-inserted in the new table. Since SQL Server by default doesn’t trust you, you need to say “OK, I know what I’m doing, now let me do my work.”

http://stackoverflow.com/questions/1969096/saving-changes-after-table-edit-in-sql-server-management-studio

Normalizing a Pre-existing Non-normalized Table #stackoverflow

Posted on by

When your table has lots of columns that need to be broken out into separate records…

It can be done with a single SELECT statement!

Needless to say, when I found this little gem of a sql statement, I was super excited.

When you have a table structured like this:

UserID  | UserName  | AnswerToQuestion1 | AnswerToQuestion2 | AnswerToQuestion31       | John      | 1                 | 0                 | 12       | Mary      | 1                 | 1                 | 0

And you want to get a result set like this (split the three data columns into three separate rows):

UserID  | UserName  | QuestionName      | Response1       | John      | AnswerToQuestion1 | 11       | John      | AnswerToQuestion2 | 01       | John      | AnswerToQuestion3 | 12       | Mary      | AnswerToQuestion1 | 12       | Mary      | AnswerToQuestion2 | 12       | Mary      | AnswerToQuestion3 | 0

Use this:

SELECT

 Y.UserID,
Y.UserName,
QuestionName = ‘AnswerToQuestion’ + X.Which,
Response =
CASE X.Which
WHEN ‘1’ THEN AnswerToQuestion1
WHEN ‘2’ THEN AnswerToQuestion2
WHEN ‘3’ THEN AnswerToQuestion3
END  

FROM YourTable Y

CROSS JOIN (SELECT ‘1’ UNION ALL SELECT ‘2’ UNION ALL SELECT ‘3’) X (Which)


http://stackoverflow.com/questions/3678479/split-multiple-columns-into-multip…

(Yes, I realize this table is still not completely normalized, but it’s a step in the right direction.)

 

Also, a related sql query: if you have a bunch of values in a single column that you need to break out into multiple rows: http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows