July 17, 2012 at 4:55 am
Hi,
I have a table like below structure
PID ID FileName FIle_Type_ID
1 1 File1 1
2 1 File2 2
3 1 File3 3
4 2 File4 1
5 2 File5 2
My output is
ID File1Name File2Name File3Name
1 File1 File2 File3
2 File4 File5 null
Please help me to write T-SQL query for the same
Thanks
July 17, 2012 at 5:28 am
July 17, 2012 at 5:30 am
sqlzealot-81 (7/17/2012)
I guess I understood the question wrongly from the topic text.
Use PIVOT/UNPIVOT for the same. Let me try and get back to you with the code.(As the number of files are not defined, its not a good idea though).
July 17, 2012 at 7:37 am
With over 700 points you are well aware of the benefits and reasons we always ask for ddl, sample data and desired output. Read the first link in my signature and post this info and you will get lots of people willing to help.
The type of query you are trying to put together can be done either with a PIVOT or using a cross tab. Generally speaking a cross tab will be better performance wise. Take a look at the links in my signature about cross tabs.
_______________________________________________________________
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/
July 18, 2012 at 5:16 am
There are many ways to do it. Here are a few....you can choose which one to use depending upon your requirement:
--Creating Table
Create Table Ex
(PID int,
ID int,
FileName Varchar(10),
FIle_Type_ID int )
--Inserting Sample Data
Insert Into Ex
Select 1, 1, 'File1', 1
Union ALL
Select 2, 1, 'File2', 2
Union ALL
Select 3, 1, 'File3', 3
Union ALL
Select 4, 2, 'File4', 1
Union ALL
Select 5, 2, 'File5', 2
--Static Pivot
Select Id, Max(FileName1) As FileName1, Max(FileName2) As FileName2, Max(FileName3) As FileName3 From
(Select Id, [FileName1], [FileName2], [FileName3] From
(Select PID, ID, FileName, 'FileName' + Cast(File_Type_Id As Varchar) As FileNames From Ex) As a
Pivot
(max(FileName) For FileNames In ([FileName1], [FileName2], [FileName3]) ) As Pvt) as b
Group By ID
--Query using Case
Select Id,
MAX(Case When FIle_Type_ID = 1 Then FileName Else '' End) As FileName1,
MAX(Case When FIle_Type_ID = 2 Then FileName Else '' End) As FileName2,
MAX(Case When FIle_Type_ID = 3 Then FileName Else '' End) As FileName3
From Ex
Group By ID
--Dynamic Pivot
Declare @cols Varchar(max), @cols1 Varchar(max), @sql Varchar(max)
Declare @temp Table(Cols Varchar(20) )
Insert Into @temp
Select Distinct FileNames From
(Select PID, ID, FileName, 'FileName' + Cast(File_Type_Id As Varchar) As FileNames From Ex) As a
Select @cols = Coalesce(@cols + ', ', '') + QUOTENAME(Cols) From @temp
Select @cols1 = Coalesce(@cols1 + '), Max(', '') + QUOTENAME(Cols) From @temp
Set @cols1 = 'Max(' + @cols1 + ')'
Set @sql = 'Select Id, '+@cols1+' From
(Select Id, '+@cols+' From
(Select PID, ID, FileName, ''FileName'' + Cast(File_Type_Id As Varchar) As FileNames From Ex) As a
Pivot
(max(FileName) For FileNames In ('+@cols+') ) As Pvt) as b
Group By ID'
Execute (@sql)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply