InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 21:55:29 GMT20http://www.sqlservercentral.com/Forums/Topic1353466-392-1.aspxHey Santa,Can you please validate the below code[code="sql"]SELECT Path,FolderName,FolderSize,FileName,FileSizeFROM( SELECT PATH,FolderName,FolderSize,'' AS FileName,'' AS FileSize, 1 AS ID FROM @Folders UNION select PATH,'' AS FolderName,'' as FolderSize,FileName, FileSize,2 AS ID from @Files )EORDER BY PATH,ID[/code]Tue, 04 Sep 2012 09:18:20 GMTMitesh Oswalhttp://www.sqlservercentral.com/Forums/Topic1353466-392-1.aspx[quote][b]mickyT (9/3/2012)[/b][hr]You could try the following query.[code="sql"]select f.Path, f.FolderName, f.FolderSize, d.FileName, d.FileSizefrom @Folders f left outer join @Files d on f.Path = d.Pathgroup by grouping sets ((f.Path, Foldername, FolderSize), (f.path, d.FileName, d.FileSize))order by f.Path, d.filename[/code]This produces the same result except there are NULLs rather than empty strings[/quote]It does, but at a cost - each table is scanned twice and there are two sorts. The UNION version scans each table once followed by a single sort. Kudos for lateral thinking though :cool:Tue, 04 Sep 2012 01:49:22 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1353466-392-1.aspxYou could try the following query.[code="sql"]select f.Path, f.FolderName, f.FolderSize, d.FileName, d.FileSizefrom @Folders f left outer join @Files d on f.Path = d.Pathgroup by grouping sets ((f.Path, Foldername, FolderSize), (f.path, d.FileName, d.FileSize))order by f.Path, d.filename[/code]This produces the same result except there are NULLs rather than empty stringsMon, 03 Sep 2012 14:50:49 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1353466-392-1.aspxYou need "placeholder" columns:[code="sql"]SELECT [Path], Foldername, FolderSize, [Filename], FileSizeFROM ( SELECT [Path], [Filename] = CAST(NULL AS Varchar(50)), FileSize = CAST(NULL AS INT), Foldername, FolderSize FROM @Folders UNION ALL SELECT [Path], [Filename], FileSize, Foldername = CAST(NULL AS Varchar(50)), FolderSize = CAST(NULL AS INT) FROM @Files) dORDER BY [Path], Foldername DESC[/code]Mon, 03 Sep 2012 06:00:48 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1353466-392-1.aspxBased off your sample data the below will union both tables together.[code="sql"]select fo.path, fo.foldername, CONVERT(VARCHAR,fo.foldersize) AS FolderSize, '' AS FileName, '' AS FileSizeFROM @Folders founionselect fi.path, '', '', fi.Filename, CONVERT(VARCHAR,fi.FileSize)from @Files fiorder by 1,4[/code]Mon, 03 Sep 2012 05:57:14 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1353466-392-1.aspxHi all, I have two tables. One has info about folders and another one about files.Following are the table[code="sql"]DECLARE @Folders TABLE( Path VARCHAR(50), Foldername Varchar(50), FolderSize INT)INSERT @FoldersSELECT 'C:\FOLDER1' Path, 'FOLDER1' Foldername, '3000' FolderSize UNION ALLSELECT 'C:\FOLDER2', 'FOLDER2', '4004' UNION ALLSELECT 'C:\FOLDER3', 'FOLDER3', '7010' SELECT * FROM @FoldersDECLARE @Files TABLE( Path VARCHAR(50), Filename Varchar(50), FileSize INT)INSERT @FilesSELECT 'C:\FOLDER1' Path, 'File1.xlsx' Filename, '1024' Filesize UNION ALLSELECT 'C:\FOLDER1' , 'File2.xlsx' , '300' UNION ALLSELECT 'C:\FOLDER1' , 'File3.xlsx' , '2000' UNION ALLSELECT 'C:\FOLDER2' , 'File4.xlsx' , '3000' UNION ALLSELECT 'C:\FOLDER2' , 'File5.xlsx' , '200' UNION ALLSELECT 'C:\FOLDER2' , 'File6.xlsx' , '3145' UNION ALLSELECT 'C:\FOLDER3' , 'File7.xlsx' , '46' UNION ALLSELECT 'C:\FOLDER3' , 'File8.xlsx' , '30' UNION ALLSELECT 'C:\FOLDER3' , 'File9.xlsx' , '2300'select * from @Files[/code]Above two table store folder and file paths .names and sizes.i want to run a select statement which will give the following result.[code="sql"]DECLARE @Result TABLE( Path VARCHAR(50),Foldername Varchar(50), FolderSize varchar(50), Filename Varchar(50), FileSize varchar(50))INSERT @ResultSELECT 'C:\FOLDER1' Path, 'FOLDER1' Foldername, '3000' FolderSize,'' Filename, '' Filesize UNION ALLSELECT 'C:\FOLDER1' , '','','File2.xlsx' , '300' UNION ALLSELECT 'C:\FOLDER1' , '','','File3.xlsx' , '2000' UNION ALLSELECT 'C:\FOLDER1' , '','','File3.xlsx' , '2000' UNION ALLSELECT 'C:\FOLDER2', 'FOLDER2', '4004','','' UNION ALLSELECT 'C:\FOLDER2' , '','','File4.xlsx' , '3000' UNION ALLSELECT 'C:\FOLDER2' , '','','File5.xlsx' , '200' UNION ALLSELECT 'C:\FOLDER2' , '','','File6.xlsx' , '3145' UNION ALLSELECT 'C:\FOLDER3', 'FOLDER3', '7010' ,'','' UNION ALLSELECT 'C:\FOLDER3' , '','','File7.xlsx' , '46' UNION ALLSELECT 'C:\FOLDER3' , '','','File8.xlsx' , '30' UNION ALLSELECT 'C:\FOLDER3' , '','','File9.xlsx' , '2300'select * from @result[/code]I have tried UNION ALL But that dont seem to work as in real data i have different number of coloumns in each table.RegardsRavi TMon, 03 Sep 2012 05:44:00 GMTsanta326