April 11, 2019 at 4:36 pm
Hello, Thanks for checking post, below is the table script and output required. Below i have provided only 4 columns, table is having more than 100 columns. If there is no difference between 2 rows based on Ldate then we can ignore. Row 2 and Row 3 having differences in last name. So i need both of them next to next.
CREATE TABLE #TEMP
(
TID VARCHAR(100),
FName VARCHAR(100),
LName VARCHAR(100),
LDate DATE
)
INSERT INTO #TEMP VALUES ('878A','user1','user2','01/01/2019') -Row1
INSERT INTO #TEMP VALUES ('878A','user1','user2','01/03/2019') -Row2
INSERT INTO #TEMP VALUES ('878A','user1','user3','01/04/2019') -Row3
------OUTPUT
COLUMNS 01/03/2019 01/04/2019
TID 878A 878A
FName User1 User1
LName User2 User3
Please let me know if any questions.
April 12, 2019 at 3:25 pm
Your desired output doesn't seem right. There is no 'user2' in FName column, there is no 'user1' in LName. How come they appear in respecting output rows. It's a crosstab query you're after, right?
--Vadim R.
April 12, 2019 at 3:43 pm
Good Catch 🙂 I have updated code now, Yes i need cross tab query. Please let me know if any questions.
April 12, 2019 at 3:56 pm
Here is one way doing this:
SELECT
[COLUMNS] = 'TID',
[2019-01-01] = MIN(CASE WHEN LDate = '20190101' THEN TID END),
[2019-01-03] = MIN(CASE WHEN LDate = '20190103' THEN TID END),
[2019-01-04] = MIN(CASE WHEN LDate = '20190104' THEN TID END)
FROM #TEMP
GROUP BY TID
UNION ALL
SELECT
[COLUMNS] = 'FName',
[2019-01-01] = MIN(CASE WHEN LDate = '20190101' THEN FName END),
[2019-01-03] = MIN(CASE WHEN LDate = '20190103' THEN FName END),
[2019-01-04] = MIN(CASE WHEN LDate = '20190104' THEN FName END)
FROM #TEMP
GROUP BY TID
UNION ALL
SELECT
[COLUMNS] = 'LName',
[2019-01-01] = MIN(CASE WHEN LDate = '20190101' THEN LName END),
[2019-01-03] = MIN(CASE WHEN LDate = '20190103' THEN LName END),
[2019-01-04] = MIN(CASE WHEN LDate = '20190104' THEN LName END)
FROM #TEMP
GROUP BY TID
Also, look at PIVOT operator: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017
Also, check SQL# library, it has some splitting functions like String_SplitIntoFields.
--Vadim R.
April 12, 2019 at 4:13 pm
You got it Thom, That's correct, Am looking for dynamic, my table contains more than 100 columns and also 90-92 rows for Each ID - EX: table contains 3months data and everyday we load same ID regardless of change/no change..and now we need change report in place. I know we could of store records which are having mismatch - but design already in place.
April 12, 2019 at 4:28 pm
Sure, if number of columns is unpredictable you'll need dynamic SQL. But inside that dynamic SQL you may implement one of the crosstab techniques. The only other way I can think of is to dynamically create crosstab temp table based on current data and populate it in the loop and/or series of Inserts/Updates from source table.
--Vadim R.
April 12, 2019 at 6:47 pm
OK, well, here's the FULLY dynamic answer. This'll handle additional columns to your table, and additional dates. It's ugly, but it works. Is this a good idea, certainly not, but I "enjoy" (is that the right word) a dynamic SQL challenge. Note, this assumes that your date s (like '01/01/2019') are in the format dd/MM/yyyy. If not, you will need to change any style code references of 103 to 101. Also note I use a real table, not a temporary table (that is important):
USE Sandbox;
GO
CREATE TABLE TEMP
(
TID VARCHAR(100),
FName VARCHAR(100),
LName VARCHAR(100),
LDate DATE
)
INSERT INTO TEMP VALUES ('878A','user1','user2','20190101')
INSERT INTO TEMP VALUES ('878A','user1','user2','20190301')
INSERT INTO TEMP VALUES ('878A','user1','user3','20190401')
GO
--SELECT *
--FROM TEMP;
GO
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT V.COLUMNS,' + NCHAR(13) + NCHAR(10) +
STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) +
N' MAX(CASE T.LDATE WHEN ' + QUOTENAME(CONVERT(varchar(8),T.LDATE,112),'''') + N' THEN CASE V.COLUMNS ' +
STUFF((SELECT NCHAR(13) + NCHAR(10) +
N' WHEN N' + QUOTENAME(C.COLUMN_NAME,'''') + N' THEN CONVERT(varchar(100),' + QUOTENAME(C.COLUMN_NAME) + N')'
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = N'TEMP'
AND COLUMN_NAME != N'LDATE'
ORDER BY ORDINAL_POSITION
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,62,N'') + NCHAR(13) + NCHAR(10) +
N' END' + NCHAR(13) + NCHAR(10) +
N' END) AS ' + QUOTENAME(CONVERT(varchar(10),LDATE,103))
FROM TEMP T
GROUP BY LDATE
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,3,N'') + NCHAR(13) + NCHAR(10) +
N'FROM TEMP T' + NCHAR(13) + NCHAR(10) +
N' CROSS APPLY (VALUES' + STUFF((SELECT N',(N' + QUOTENAME(COLUMN_NAME,'''') + N')'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'TEMP'
AND COLUMN_NAME != N'LDATE'
ORDER BY ORDINAL_POSITION ASC
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,1,N'') + N') V(COLUMNS)' + NCHAR(13) + NCHAR(10) +
N'GROUP BY V.COLUMNS;';
PRINT @SQL; --Your debugging best friend
EXEC sp_executesql @SQL;
GO
DROP TABLE TEMP;
The above SQL produces, and executes, the below SQL (with formatting, which is why my SQL looks the way it does):
SELECT V.COLUMNS,
MAX(CASE T.LDATE WHEN '20190101' THEN CASE V.COLUMNS WHEN N'TID' THEN [TID]
WHEN N'FName' THEN [FName]
WHEN N'LName' THEN [LName]
END
END) AS [01/01/2019],
MAX(CASE T.LDATE WHEN '20190301' THEN CASE V.COLUMNS WHEN N'TID' THEN [TID]
WHEN N'FName' THEN [FName]
WHEN N'LName' THEN [LName]
END
END) AS [01/03/2019],
MAX(CASE T.LDATE WHEN '20190401' THEN CASE V.COLUMNS WHEN N'TID' THEN [TID]
WHEN N'FName' THEN [FName]
WHEN N'LName' THEN [LName]
END
END) AS [01/04/2019]
FROM TEMP T
CROSS APPLY (VALUES(N'TID'),(N'FName'),(N'LName')) V(COLUMNS)
GROUP BY V.COLUMNS;
And produces the results below:
COLUMNS 01/01/2019 01/03/2019 01/04/2019
TID 878A 878A 878A
FName user1 user1 user1
LName user2 user2 user3
But, it's not up to be to understand what this does and how it works, it's up to you koti.raavi. If you don't understand it, I can try to explain some of it, however, that is far from entry level SQL. If you don't understand it in it's entirety use it with caution; I cannot and will not be able to support the above code.
Edit: Wow, that looks awful on the forums... Just, awful. I suggest pasting that into SSMS, or ADS (Azure Data Studio) to see what the above looks like.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 13, 2019 at 8:50 am
It does look good, it worked almost 98%, but issue is here with order, the few columns are added to table later - I mean not with original table creation, I cross checked in Information Columns table those are in correct order. but query pulling them in top position .
Thank you So Much For Help!
April 13, 2019 at 9:18 am
It does look good, it worked almost 98%, but issue is here with order, the few columns are added to table later - I mean not with original table creation, I cross checked in Information Columns table those are in correct order. but query pulling them in top position . Thank you So Much For Help!
Not sure what you mean here. The columns are sorted by ordinal position (see the ORDER BY ORDINAL_POSITION ASC clause in one of the statements). New columns in the table TEMP will be added the bottom of the dataset in the dynamic statement.
I did, however, miss out ORDER BY LDATE after GROUP BY LDATE; perhaps that the problem you're having? I can't see what you see so I'm working blind here.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 13, 2019 at 9:22 am
DB<>Fiddle showing "working as intended".
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply