{"id":379,"date":"2008-02-14T23:47:46","date_gmt":"2008-02-14T22:47:46","guid":{"rendered":"http:\/\/soci.hu\/blog\/index.php\/2008\/02\/14\/sql-server-2008-ujdonsagok-21-row-constructor\/"},"modified":"2008-02-14T23:48:54","modified_gmt":"2008-02-14T22:48:54","slug":"sql-server-2008-ujdonsagok-21-row-constructor","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2008\/02\/14\/sql-server-2008-ujdonsagok-21-row-constructor\/","title":{"rendered":"SQL Server 2008 \u00fajdons\u00e1gok 21. &#8211; Row constructor"},"content":{"rendered":"<p>Amikor p\u00e1r sornyi be\u00e9p\u00edtett adattal kell dolgozni, j\u00f3l j\u00f6n, hogy van t\u00e1bla t\u00edpus\u00fa v\u00e1ltoz\u00f3:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDECLARE @Movie TABLE\r\n(\r\n    MovieID INT IDENTITY(1, 1),\r\n    MovieRatingId INT,\r\n    Title VARCHAR(200) NOT NULL\r\n);\r\n<\/pre>\n<p>Az adatok bet\u00f6lt\u00e9se azonban egy utas\u00edt\u00e1sban kicsit k\u00f6r\u00fclm\u00e9nyes volt:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nINSERT INTO @Movie (MovieRatingId, Title)\r\nSELECT 3, &#039;SQL the Movie&#039;\r\nUNION ALL\r\nSELECT 4, &#039;SQL Massacre&#039;\r\nUNION ALL\r\nSELECT 1, &#039;SQL for Everyone&#039;\r\nUNION ALL\r\nSELECT 4, &#039;SQL Massacre 2 - The Oracle Returns&#039;\r\n<\/pre>\n<p>De SQL Server 2008-ban l\u00e9tezik a Row Constructor, azzal sokkal tiszt\u00e1bb megold\u00e1st \u00e9rhet\u00fcnk el:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nINSERT INTO @Movie (MovieRatingId, Title)\r\nVALUES\r\n(3, &#039;SQL the Movie&#039;),\r\n(4, &#039;SQL Massacre&#039;),\r\n(1, &#039;SQL for Everyone&#039;),\r\n(4, &#039;SQL Massacre 2 - The Oracle Returns&#039;);\r\n<\/pre>\n<p>J\u00f3, mi?<\/p>\n<p>K\u00f6zvetlen\u00fcl is lehet szelekt\u00e1lni VALUES-zal l\u00e9trehozott \u00e1l-t\u00e1bl\u00e1b\u00f3l, csak ilyenkor meg kell adni az oszlopok nev\u00e9t is az AS ut\u00e1n, ami \u00e9rhet\u0151 k\u00f6vetelm\u00e9ny.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT * FROM (\r\nVALUES\r\n(3, &#039;SQL the Movie&#039;),\r\n(4, &#039;SQL Massacre&#039;),\r\n(1, &#039;SQL for Everyone&#039;),\r\n(4, &#039;SQL Massacre 2 - The Oracle Returns&#039;)) AS T(id, text)\r\n<\/pre>\n<p>Mindez persze megy CTE-ben is:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nWITH MovieRatings (MovieRatingId, Code, Description, AllowYouthRentalFlag)\r\nAS\r\n(   \r\nSELECT *\r\n    FROM (VALUES\r\n    (0, &#039;UR&#039;,&#039;Unrated&#039;,1),\r\n    (1, &#039;G&#039;,&#039;General Audiences&#039;,1),\r\n    (2, &#039;PG&#039;,&#039;Parental Guidance&#039;,1),\r\n    (3, &#039;PG-13&#039;,&#039;Parental Guidance for Children Under 13&#039;,1),\r\n    (4, &#039;R&#039;,&#039;Restricted, No Children Under 17 without Parent&#039;,0))\r\n        AS MR(MovieRatingId, Code, Description, AllowYouthRentalFlag)\r\n)\r\nSELECT\r\nM.Title,\r\nMR.Code AS RatingCode,\r\nMR.Description AS RatingDescription\r\nFROM @Movie AS M\r\nINNER JOIN MovieRatings AS MR\r\nON M.MovieRatingId = MR.MovieRatingId;\r\n<\/pre>\n<p>Ezek ut\u00e1n n\u00e9ha m\u00e1r nem is kell t\u00e1bla t\u00edpus\u00fa v\u00e1ltoz\u00f3, el\u00e9g egy ilyen pszeudo t\u00e1bla.<\/p>\n<p>P\u00e9ld\u00e1k <a href=\"http:\/\/debetta.com\/blogs\/peter_debetta\/archive\/2007\/12\/05\/39.aspx\">innen<\/a> lopva. K\u00f6sz\u00f6net \u00e9rte a szerz\u0151nek.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Amikor p\u00e1r sornyi be\u00e9p\u00edtett adattal kell dolgozni, j\u00f3l j\u00f6n, hogy van t\u00e1bla t\u00edpus\u00fa v\u00e1ltoz\u00f3: DECLARE @Movie TABLE ( MovieID INT IDENTITY(1, 1),&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,58],"tags":[],"class_list":["post-379","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/379","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=379"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/379\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=379"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=379"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=379"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}